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?
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 |
+--------------------------------------------+
Do I understand right that you assume that DATE_ADD("2011-01-01", INTERVAL 6 MONTH)
should give you '2011-06-30' instead of '2011-07-01'? Of course, 2011-01-01 + 6 months is 2011-07-01. You want something like DATE_SUB(DATE_ADD("2011-01-01", INTERVAL 6 MONTH), INTERVAL 1 DAY)
.
If expr is greater than or equal to min and expr is less than or equal to max,
BETWEEN
returns 1, otherwise it returns 0.
The important part here is EQUAL to max., which 1st of July is.
DATE_ADD
works correctly. 1 January plus 6 months is 1 July, just like 1 January plus 1 month is 1 of February.
Between operation is inclusive. So, you are getting everything up to, and including, 1 July. (see also MySQL "between" clause not inclusive?)
What you need to do is subtract 1 day or use < operator instead of between.
Source: Stackoverflow.com