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?
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