make differential in DATE query in MySQL

Question

what is the different from this mysql query:

WHERE MONTH(date) = MONTH(CURRENT_DATE)

and this

WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()

i have tried both but i cant see the different.

Answer 1

The first one

WHERE MONTH(date) = MONTH(CURRENT_DATE)

will select rows where the month of the date column is the same as the current month (e.g. all rows for which the month of the date field is September.

The second one

WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()

will select rows for which the date field is between now and a month ago.

Answer 2

For today's CURRENT_DATE, i.e.: 23-September-2010:

WHERE MONTH(date) = MONTH(CURRENT_DATE) is date also in September, i.e. between 01-September and 30-September of any year.

WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE() is date within the last 30/31 days. If we're on 23rd, this will give you an interval between 23-August-2010 and 23-September-2010.