DATE_ADD(date,INTERVAL expr unit)

MySQL 8.4

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

Syntax

MYSQL

SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
'2018-05-02'
SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
'2017-05-01'
SELECT DATE_ADD('2020-12-31 23:59:59',
                INTERVAL 1 SECOND);
'2021-01-01 00:00:00'
SELECT DATE_ADD('2018-12-31 23:59:59',
                INTERVAL 1 DAY);
'2019-01-01 23:59:59'
SELECT DATE_ADD('2100-12-31 23:59:59',
                INTERVAL '1:1' MINUTE_SECOND);
'2101-01-01 00:01:00'
SELECT DATE_SUB('2025-01-01 00:00:00',
                INTERVAL '1 1:1:1' DAY_SECOND);
'2024-12-30 22:58:59'
SELECT DATE_ADD('1900-01-01 00:00:00',
                INTERVAL '-1 10' DAY_HOUR);
'1899-12-30 14:00:00'
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
'1997-12-02'
SELECT DATE_ADD('1992-12-31 23:59:59.000002',
           INTERVAL '1.999999' SECOND_MICROSECOND);
'1993-01-01 00:00:01.000001'

Examples

Example

These functions perform date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is evaluated as a string; it may start with a - for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted.

MYSQL

SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
'2018-05-02'
SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
'2017-05-01'
SELECT DATE_ADD('2020-12-31 23:59:59',
                INTERVAL 1 SECOND);
'2021-01-01 00:00:00'
SELECT DATE_ADD('2018-12-31 23:59:59',
                INTERVAL 1 DAY);
'2019-01-01 23:59:59'
SELECT DATE_ADD('2100-12-31 23:59:59',
                INTERVAL '1:1' MINUTE_SECOND);
'2101-01-01 00:01:00'
SELECT DATE_SUB('2025-01-01 00:00:00',
                INTERVAL '1 1:1:1' DAY_SECOND);
'2024-12-30 22:58:59'
SELECT DATE_ADD('1900-01-01 00:00:00',
                INTERVAL '-1 10' DAY_HOUR);
'1899-12-30 14:00:00'
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
'1997-12-02'
SELECT DATE_ADD('1992-12-31 23:59:59.000002',
           INTERVAL '1.999999' SECOND_MICROSECOND);
'1993-01-01 00:00:01.000001'

Example 2

For more information about temporal interval syntax, including a full list of unit specifiers, the expected form of the expr argument for each unit value, and rules for operand interpretation in temporal arithmetic, see Temporal Intervals.

MYSQL

SELECT DATE_ADD('2024-03-30', INTERVAL 1 MONTH) AS d1,
     >        DATE_ADD('2024-03-31', INTERVAL 1 MONTH) AS d2;
+------------+------------+
| d1         | d2         |
+------------+------------+
| 2024-04-30 | 2024-04-30 |
+------------+------------+
1 row in set (0.00 sec)