Rectangle 27 1

sql MySQL DATE_ADD month interval?


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

In fact the desired result for me would be to have "2011-06-30", but don't really know how to get it...

The DATE_SUB works fine, thank you. In fact, i focused on the 6 months interval, and didn't really thjough about removing one day afterwards x(

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

or you explicitly have to use > and < instead of BETWEEN

Note
Rectangle 27 1

sql MySQL DATE_ADD month interval?


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

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.

That's exactly what I wanted :) Thank you, didn't know we could use more than one interval with DATE_ADD.

btw, "INTERVAL 6 MONTHS" should be "INTERVAL 6 MONTH". Note the lack of the 'S' at the end.

Note
Rectangle 27 1

sql MySQL DATE_ADD month interval?


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

In fact the desired result for me would be to have "2011-06-30", but don't really know how to get it...

The DATE_SUB works fine, thank you. In fact, i focused on the 6 months interval, and didn't really thjough about removing one day afterwards x(

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

or you explicitly have to use > and < instead of BETWEEN

Note
Rectangle 27 1

sql MySQL DATE_ADD month interval?


BETWEEN ... AND

I know what BETWEEN is, but ni my context, I can't use it.

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.

Note
Rectangle 27 1

sql MySQL DATE_ADD month interval?


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

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.

That's exactly what I wanted :) Thank you, didn't know we could use more than one interval with DATE_ADD.

btw, "INTERVAL 6 MONTHS" should be "INTERVAL 6 MONTH". Note the lack of the 'S' at the end.

Note
Rectangle 27 1

sql MySQL DATE_ADD month interval?


BETWEEN ... AND

I know what BETWEEN is, but ni my context, I can't use it.

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.

Note
Rectangle 27 1

sql MySQL DATE_ADD month interval?


DATE_ADD("2011-01-01", INTERVAL 6 MONTH)
DATE_SUB(DATE_ADD("2011-01-01", INTERVAL 6 MONTH), INTERVAL 1 DAY)
Note
Rectangle 27 1

sql MySQL DATE_ADD month interval?


As the "6" is a dynamic number, I can't use BETWEEN in my request.

Between operation is inclusive. So, you are getting everything up to, and including, 1 July. (see also MySQL "between" clause not inclusive?)

DATE_ADD works correctly. 1 January plus 6 months is 1 July, just like 1 January plus 1 month is 1 of February.

What you need to do is subtract 1 day or use < operator instead of between.

Note