MySQL DATE_ADD Function
Summary: in this tutorial, you will learn how to use MySQL DATE_ADD function to add a time value to a DATE or DATETIME value.
Introduction to MySQL DATE_ADD function
The DATE_ADD function adds an interval to a DATE or DATETIME value. The following illustrates the syntax of the DATE_ADD function:
DATE_ADD(start_date, INTERVAL expr unit);
Code language: SQL (Structured Query Language) (sql)
The DATE_ADD function takes two arguments:
start_dateis a startingDATEorDATETIMEvalueINTERVAL expr unitis an interval value to be added to the starting date value.
The DATE_ADD function may return a DATETIME value or a string, depending on the arguments:
DATETIMEif the first argument is aDATETIMEvalue or if the interval value has time element such as hour, minute or second, etc.- String otherwise.
MySQL DATE_ADD function examples
Let’s take a look few examples to understand how DATE_ADD function works.
Add 1 second to 1999-12-31 23:59:59:
SELECT
DATE_ADD('1999-12-31 23:59:59',
INTERVAL 1 SECOND) result;| result |
+———————+
| 2000-01-01 00:00:00 |
+———————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Add 1 day to 1999-12-31 00:00:01:
SELECT
DATE_ADD('1999-12-31 00:00:01',
INTERVAL 1 DAY) result;| result |
+———————+
| 2000-01-01 00:00:01 |
+———————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Add 1 minute and 1 second to 1999-12-31 23:59:59.
SELECT
DATE_ADD('1999-12-31 23:59:59',
INTERVAL '1:1' MINUTE_SECOND) result;| result |
+———————+
| 2000-01-01 00:01:00 |
+———————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Add -1 day and 5 hours to 2000-01-01 00:00:00.
SELECT DATE_ADD('2000-01-01 00:00:00',
INTERVAL '-1 5' DAY_HOUR) result;| result |
+———————+
| 1999-12-30 19:00:00 |
+———————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Add 1 second and 999999 microseconds to 1999-12-31 23:59:59.000002:
SELECT
DATE_ADD('1999-12-31 23:59:59.000002',
INTERVAL '1.999999' SECOND_MICROSECOND) result;| result |
+—————————-+
| 2000-01-01 00:00:01.000001 |
+—————————-+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
MySQL DATE_ADD function usage notes
Interval Handling
In the interval:
INTERVAL expr unit
Code language: SQL (Structured Query Language) (sql)
The expr is treated as a string, therefore, you should be careful when you use a non-string value for the expr. For example, with an interval of HOUR_MINUTE, 5/2 evaluates to 2.5000 (not 2.5) and is treated as 2 hours 5000 minutes as in the following statement:
SELECT
DATE_ADD('2000-01-01',
INTERVAL 5 / 2 HOUR_MINUTE) result;| result |
+———————+
| 2000-01-04 13:20:00 |
+———————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
To ensure the correct interpretation of a non-string interval value, you should use the CAST function as follows:
SELECT
DATE_ADD('2000-01-01',
INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE) result;| result |
+———————+
| 2000-01-01 01:05:00 |
+———————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Automatic DATETIME conversion
If you add a time value to a date value, the result is a DATETIME value as shown in the following example:
SELECT
DATE_ADD('2000-01-01', INTERVAL 12 HOUR) result;| result |
+———————+
| 2000-01-01 12:00:00 |
+———————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Invalid starting date
The DATE_ADD function returns NULL if you use an invalid date for the first argument, for example:
SELECT DATE_ADD('2000-02-30',
INTERVAL 1 DAY) result;
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set, 1 warning (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
If you want to see the warning in detail, you use the SHOW WARNINGS statement:
SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2000-02-30' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Adjusted day, month, or year
If you add an interval of MONTH, YEAR, or YEAR_MONTH to a date that results in a date which has a day larger than the maximum day for the new month, the day will be adjusted to the maximum day in the new month.
Consider the following example:
SELECT
DATE_ADD('2010-01-30',
INTERVAL 1 MONTH) result;| result |
+————+
| 2010-02-28 |
+————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this example, we added 1 month to the January 30th 2010 that results in February 28th 2010. The day was adjusted to the maximum day inFebruary 2010.
In the year that the February has 29 days, the date will be also adjusted to 29th as shown below:
SELECT
DATE_ADD('2012-01-30',
INTERVAL 1 MONTH) result;| result |
+————+
| 2012-02-29 |
+————+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL DATE_ADD function to add an interval to a DATE or DATETIME value.