DATE_ADD

The DATE_ADD() function adds the given interval of time to a date or datetime object.

Syntax

DATE_ADD (dateobj, INTERVAL expr unit)
ADDDATE (dateobj, INTERVAL expr unit)
ADDDATE (dateobj, days)

Arguments

  • dateobj: a valid date, datetime, or parsable date string

  • expr: the number of units to add if unit is a simple type, or a string representation of the units to add if unit is a complex type. Can be negative.

  • days: number of days to add. Can be negative.

Simple types allowed for unit include YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND. Complex types allowed for unit include YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND. The expected format of expr for complex unit types is an arbitrarily delimited string of integers.

Return Type

Date or datetime object. If dateobj is not a valid date, returns NULL.

Remarks

  • In DATE_ADD() and ADDDATE() functions, MOD(...) and a MOD b expressions in INTERVAL expr unit syntax are not supported without parentheses due to parser limitations. Use the modulo operator (%), or wrap the MOD expression in parentheses.

    -- Supported
    SELECT DATE_ADD('2020-01-01', INTERVAL 100 % 3 DAY);
    SELECT DATE_ADD('2020-01-01', INTERVAL (MOD(100, 3)) DAY);
    SELECT DATE_ADD('2020-01-01', INTERVAL (100 MOD 3) DAY);
    -- Not supported
    DATE_ADD('2020-01-01', INTERVAL MOD(100, 3) DAY);
    DATE_ADD('2020-01-01', INTERVAL 100 MOD 3 DAY);

Examples

SELECT ADDDATE("2010-04-02 01:23:54", INTERVAL 1 YEAR);
+-------------------------------------------------+
| ADDDATE("2010-04-02 01:23:54", INTERVAL 1 YEAR) |
+-------------------------------------------------+
| 2011-04-02 01:23:54                             |
+-------------------------------------------------+
SELECT ADDDATE("2010-04-02", INTERVAL "1 02:03:04.567" DAY_MICROSECOND);
+------------------------------------------------------------------+
| ADDDATE("2010-04-02", INTERVAL "1 02:03:04.567" DAY_MICROSECOND) |
+------------------------------------------------------------------+
| 2010-04-03 02:03:04.567000                                       |
+------------------------------------------------------------------+
SELECT Emp_No, Hire_Date, DATE_ADD(Hire_Date, INTERVAL 3 MONTH) Contract_End_Date FROM Employees WHERE Emp_No = 12345;
+--------+------------+-------------------+
| Emp_No | Hire_Date  | Contract_End_Date |
+--------+------------+-------------------+
|  12345 | 2014-10-28 | 2015-01-28        |
+--------+------------+-------------------+

Last modified:

Was this article helpful?