MySQL - DATE_ADD month interval

Question

I face a problem with the function DATE_ADD in MySQL.

My request looks like this :

SELECT *  FROM mydb  WHERE creationdate BETWEEN "2011-01-01" AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH)  GROUP BY MONTH(creationdate) 

The problem is that, in the results, -I think- because June has only 30 days, the function doesn't work properly as I have the results of the first of July.

Is there a way to tell DATE_ADD to work well and take the right number of days within a month?

Answer 1

DATE_ADD works just fine with different months. The problem is that you are adding six months to 2001-01-01 and July 1st is supposed to be there.

This is what you want to do:

SELECT *  FROM mydb  WHERE creationdate BETWEEN "2011-01-01"                     AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH) - INTERVAL 1 DAY GROUP BY MONTH(creationdate) 

OR

SELECT *  FROM mydb  WHERE creationdate >= "2011-01-01"  AND creationdate < DATE_ADD("2011-01-01", INTERVAL 6 MONTH) GROUP BY MONTH(creationdate) 

For further learning, take a look at DATE_ADD documentation.

*edited to correct syntax

Answer 2

Well, for me this is the expected result; adding six months to Jan. 1st July.

mysql> SELECT DATE_ADD( '2011-01-01', INTERVAL 6 month ); +--------------------------------------------+ | DATE_ADD( '2011-01-01', INTERVAL 6 month ) | +--------------------------------------------+ | 2011-07-01                                 |  +--------------------------------------------+