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?
The DATE_ADD() function adds a time/date interval to a date and then returns the date.
DATE_ADD() function in MySQL is used to add a specified time or date interval to a specified date and then return the date. Specified date to be modified. Here the value is the date or time interval to add.
The TIMEDIFF() function returns the difference between two time/datetime expressions. Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.
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
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 | +--------------------------------------------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With