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_date is a starting DATE or DATETIME value.
INTERVAL expr unit is 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:

DATETIME if the first argument is a DATETIME value or if the interval value has a time element such as hour, minute, second, etc.
String otherwise.

MySQL DATE_ADD function examples

Let’s take a look at some examples to understand how DATE_ADD() function works.

The following statement uses the DATE_ADD() function to add one second to 1999-12-31 23:59:59:

SELECT 
    DATE_ADD('1999-12-31 23:59:59',
        INTERVAL 1 SECOND) result;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| result              |
+---------------------+
| 2000-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following example uses the DATE_ADD() function to add one day to 1999-12-31 00:00:01:

SELECT 
    DATE_ADD('1999-12-31 00:00:01',
        INTERVAL 1 DAY) result;    Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| result              |
+---------------------+
| 2000-01-01 00:00:01 |
+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following example uses the DATE_ADD() function to 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;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| result              |
+---------------------+
| 2000-01-01 00:01:00 |
+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following example adds -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;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| result              |
+---------------------+
| 1999-12-30 19:00:00 |
+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following example adds 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;
Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------+
| result                     |
+----------------------------+
| 2000-01-01 00:00:01.000001 |
+----------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

MySQL DATE_ADD function usage notes

Interval Handling

In the interval:

INTERVAL expr unitCode 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;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| result              |
+---------------------+
| 2000-01-04 13:20:00 |
+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

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;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| result              |
+---------------------+
| 2000-01-01 01:05:00 |
+---------------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)

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;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| result              |
+---------------------+
| 2000-01-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

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;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
| NULL   |
+--------+
1 row in set, 1 warning (0.00 sec)Code language: JavaScript (javascript)

If you want to see the warning in detail, you use the SHOW WARNINGS statement:

SHOW WARNINGS;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2000-02-30' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Adjusted day, month, or year

If you add an interval of MONTH, YEAR, or YEAR_MONTH to a date that results in a date that 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;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+
| result     |
+------------+
| 2010-02-28 |
+------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

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 February has 29 days, the date will be also adjusted to the 29th as shown below:

SELECT 
    DATE_ADD('2012-01-30', 
            INTERVAL 1 MONTH) result;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+
| result     |
+------------+
| 2012-02-29 |
+------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

Use the MySQL DATE_ADD function to add an interval to a DATE or DATETIME value.

Was this tutorial helpful?