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?

score 68 · Accepted Answer · edited Feb 12 '15 at 00:53

68

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

edited Feb 12 '15 at 00:53

Ben Harvey

1,793
2
16
23

answered Jul 27 '11 at 14:03

Adriano Carneiro

57,693
12
90
123

That's exactly what I wanted :) Thank you, didn't know we could use more than one interval with DATE_ADD. – BMN Jul 27 '11 at 14:08
6

btw, "INTERVAL 6 MONTHS" should be "INTERVAL 6 MONTH". Note the lack of the 'S' at the end. – James John McGuire 'Jahmic' Apr 18 '14 at 05:49
That's because this is SQL, not your language. It means: INTERVAL AMOUNT TYPE – Leandro Bardelli Oct 09 '20 at 06:50

score 6 · Answer 2 · answered Jul 27 '11 at 14:03

6

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

answered Jul 27 '11 at 14:03

wonk0

13,402
1
21
15

In fact the desired result for me would be to have "2011-06-30", but don't really know how to get it... – BMN Jul 27 '11 at 14:05
DATE_SUB( DATE_ADD( '2011-01-01', INTERVAL 6 month ), INTERVAL 1 day ) – wonk0 Jul 27 '11 at 14:08
or you explicitly have to use > and < instead of BETWEEN – wonk0 Jul 27 '11 at 14:09
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( – BMN Jul 27 '11 at 14:14

score 1 · Answer 3 · edited Jul 27 '11 at 14:05

1

BETWEEN ... AND

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.

edited Jul 27 '11 at 14:05

Adriano Carneiro

57,693
12
90
123

answered Jul 27 '11 at 14:02

Jacob

41,721
6
79
81

I know what BETWEEN is, but ni my context, I can't use it. – BMN Jul 27 '11 at 14:08

score 1 · Answer 4 · answered Jul 27 '11 at 14:06

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).

score 0 · Answer 5 · edited May 23 '17 at 12:09

0

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.

edited May 23 '17 at 12:09

Community

1
1

answered Jul 27 '11 at 14:04

yu_sha

4,290
22
19

As the "6" is a dynamic number, I can't use BETWEEN in my request. – BMN Jul 27 '11 at 14:06

MySQL - DATE_ADD month interval

5 Answers5

Linked