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

User · Answer

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  quot between quot  clause not inclusive    What you need to do is subtract 1 day or use  lt  operator instead of between

User · Answer

Well  for me this is the expected result  adding six months to Jan  1st July    mysql gt  SELECT DATE ADD   2011-01-01   INTERVAL 6 month     --------------------------------------------    DATE ADD   2011-01-01   INTERVAL 6 month      --------------------------------------------    2011-07-01                                     --------------------------------------------

User · Answer

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  gt    2011-01-01   AND creationdate  lt  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

User · Answer

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

User · Answer

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

[mysql] MySQL - DATE_ADD month interval

Examples related to mysql

Examples related to sql

Examples related to select

Examples related to dateadd