[mysql] MySQL - DATE_ADD month interval

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?

This question is related to mysql sql select dateadd

The answer is


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


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.


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.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to select

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to dateadd

How to add days to the current date? How to select last one week data from today's date Add 'x' number of hours to date MySQL - DATE_ADD month interval Subtract minute from DateTime in SQL Server 2005 SQL Server 2005 Using DateAdd to add a day to a date