DATE_ADD function to add and subtract days from a date - MaxCompute

Adds or subtracts a number of days from a date. Returns the result as a DATE value in yyyy-mm-dd format.

For sub-day precision (hours, minutes, or seconds), use DATEADD instead. To offset from the current date, combine DATE_ADD with GETDATE. DATE_ADD is the inverse of DATE_SUB.

Syntax

DATE DATE_ADD(DATE|TIMESTAMP|STRING <startdate>, BIGINT <delta>)

Parameters

Parameter	Required	Type	Description
`startdate`	Yes	DATE, DATETIME, or STRING	The start date. For STRING inputs, the value must include at least the `yyyy-mm-dd` portion (for example, `'2025-07-27'`). In a MaxCompute V1.0 data type edition project, STRING values are implicitly converted to DATE before the calculation.
`delta`	Yes	BIGINT	The number of days to add or subtract. Positive values add days; negative values subtract days; `0` leaves the date unchanged.

Return value

Returns a DATE value in yyyy-mm-dd format.

Condition	Result
`startdate` is NULL	Error
`startdate` is not DATE, DATETIME, or STRING	Error
`delta` is NULL	NULL

Usage notes

Month-end rollover: When the result would exceed the last day of a month, the function rolls over to the next month. For example, DATE_ADD(DATE '2005-02-28', 1) returns 2005-03-01.
DATETIME and TIMESTAMP inputs: The time portion is ignored. The function always returns a DATE value, regardless of whether startdate is a DATE, DATETIME, or TIMESTAMP.
V2.0 data types: To use DATETIME or TIMESTAMP inputs, enable the MaxCompute V2.0 data type edition by running SET odps.sql.type.system.odps2=true. Submit this statement in the same session as your SQL statement.

Examples

Add days

-- Returns 2005-03-01. Adding 1 day to Feb 28 rolls over to March.
SELECT DATE_ADD(DATETIME '2005-02-28 00:00:00', 1);

Subtract days

-- Returns 2005-02-27.
SELECT DATE_ADD(DATE '2005-02-28', -1);

Use a STRING input (V1.0 data type edition)

-- Returns 2005-03-20. SET must be submitted together with the SQL statement.
SET odps.sql.type.system.odps2=false;
SELECT DATE_ADD('2005-02-28 00:00:00', 20);

Offset from today

-- Returns yesterday's date. If today is 2020-11-17, returns 2020-11-16.
SELECT DATE_ADD(GETDATE(), -1);

Handle a NULL delta

-- Returns NULL.
SELECT DATE_ADD('2005-02-28 00:00:00', null);

Apply to table data

The following example uses the mf_date_fun_t sample table. For the table definition and data, see Sample data.

All three calls use V2.0 data types. date1 moves forward 1 day, datetime1 moves back 1 day, and timestamp1 is unchanged (delta = 0).

-- Enable the MaxCompute V2.0 data type edition.
-- This SET statement must be submitted together with the SQL statement.
SET odps.sql.type.system.odps2=true;
SELECT date1,
       DATE_ADD(date1, 1)       AS date1_date_add,
       datetime1,
       DATE_ADD(datetime1, -1)  AS datetime1_date_add,
       timestamp1,
       DATE_ADD(timestamp1, 0)  AS timestamp1_date_add
FROM mf_date_fun_t;

Result:

+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+
| date1      | date1_date_add | datetime1           | datetime1_date_add | timestamp1                    | timestamp1_date_add |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+
| 2021-11-29 | 2021-11-30     | 2021-11-29 00:01:00 | 2021-11-28         | 2021-01-11 00:00:00.123456789 | 2021-01-11          |
| 2021-11-28 | 2021-11-29     | 2021-11-28 00:02:00 | 2021-11-27         | 2021-02-11 00:00:00.123456789 | 2021-02-11          |
| 2021-11-27 | 2021-11-28     | 2021-11-27 00:03:00 | 2021-11-26         | 2021-03-11 00:00:00.123456789 | 2021-03-11          |
| 2021-11-26 | 2021-11-27     | 2021-11-26 00:04:00 | 2021-11-25         | 2021-04-11 00:00:00.123456789 | 2021-04-11          |
| 2021-11-25 | 2021-11-26     | 2021-11-25 00:05:00 | 2021-11-24         | 2021-05-11 00:00:00.123456789 | 2021-05-11          |
| 2021-11-24 | 2021-11-25     | 2021-11-24 00:06:00 | 2021-11-23         | 2021-06-11 00:00:00.123456789 | 2021-06-11          |
| 2021-11-23 | 2021-11-24     | 2021-11-23 00:07:00 | 2021-11-22         | 2021-07-11 00:00:00.123456789 | 2021-07-11          |
| 2021-11-22 | 2021-11-23     | 2021-11-22 00:08:00 | 2021-11-21         | 2021-08-11 00:00:00.123456789 | 2021-08-11          |
| 2021-11-21 | 2021-11-22     | 2021-11-21 00:09:00 | 2021-11-20         | 2021-09-11 00:00:00.123456789 | 2021-09-11          |
| 2021-11-20 | 2021-11-21     | 2021-11-20 00:10:00 | 2021-11-19         | 2021-10-11 00:00:00.123456789 | 2021-10-11          |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+

Sample data

The examples above reference the mf_date_fun_t table. Run the following statements to create and populate it.

CREATE TABLE IF NOT EXISTS mf_date_fun_t (
    id         INT,
    date1      DATE,
    datetime1  DATETIME,
    timestamp1 TIMESTAMP,
    date2      DATE,
    datetime2  DATETIME,
    timestamp2 TIMESTAMP,
    date3      STRING,
    date4      BIGINT
);

INSERT INTO mf_date_fun_t VALUES
(1,  DATE'2021-11-29', DATETIME'2021-11-29 00:01:00', TIMESTAMP'2021-01-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-20', 123456780),
(2,  DATE'2021-11-28', DATETIME'2021-11-28 00:02:00', TIMESTAMP'2021-02-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-21', 123456781),
(3,  DATE'2021-11-27', DATETIME'2021-11-27 00:03:00', TIMESTAMP'2021-03-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-22', 123456782),
(4,  DATE'2021-11-26', DATETIME'2021-11-26 00:04:00', TIMESTAMP'2021-04-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-23', 123456783),
(5,  DATE'2021-11-25', DATETIME'2021-11-25 00:05:00', TIMESTAMP'2021-05-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-24', 123456784),
(6,  DATE'2021-11-24', DATETIME'2021-11-24 00:06:00', TIMESTAMP'2021-06-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-25', 123456785),
(7,  DATE'2021-11-23', DATETIME'2021-11-23 00:07:00', TIMESTAMP'2021-07-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-26', 123456786),
(8,  DATE'2021-11-22', DATETIME'2021-11-22 00:08:00', TIMESTAMP'2021-08-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-27', 123456787),
(9,  DATE'2021-11-21', DATETIME'2021-11-21 00:09:00', TIMESTAMP'2021-09-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-28', 123456788),
(10, DATE'2021-11-20', DATETIME'2021-11-20 00:10:00', TIMESTAMP'2021-10-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-29', 123456789);

To verify the data:

SELECT * FROM mf_date_fun_t;

+------+------------+---------------------+-------------------------------+------------+---------------------+-------------------------------+------------+------------+
| id   | date1      | datetime1           | timestamp1                    | date2      | datetime2           | timestamp2                    | date3      | date4      |
+------+------------+---------------------+-------------------------------+------------+---------------------+-------------------------------+------------+------------+
| 1    | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-20 | 123456780  |
| 2    | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-21 | 123456781  |
| 3    | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-22 | 123456782  |
| 4    | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-23 | 123456783  |
| 5    | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-24 | 123456784  |
| 6    | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-25 | 123456785  |
| 7    | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-26 | 123456786  |
| 8    | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-27 | 123456787  |
| 9    | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-28 | 123456788  |
| 10   | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-29 | 123456789  |
+------+------------+---------------------+-------------------------------+------------+---------------------+-------------------------------+------------+------------+

Related functions

DATE_ADD is a date function. For more information about date calculation and conversion functions, see Date functions.