DATE_ADD

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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.

Examples

SQL

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

SQL

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

SQL

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: May 31, 2023