MySQL DATE_ADD Function

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

The DATE_ADD the 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 a 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 time elements such as hour, a minute, or second, etc.
• String otherwise.

MySQL DATE_ADD function examples

Let’s take a look at a 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)

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)

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)

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)

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)

MySQL DATE_ADD function usage notes

Interval Handling

In the interval:

INTERVAL expr unit

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)

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)

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)

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)

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)

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)

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

In this tutorial, you have learned how to use the MySQL DATE_ADD function to add an interval to a DATE or DATETIME value.