Use the DATEADD function to add or subtract a time interval from a date or timestamp - MaxCompute

Adds or subtracts a time interval from a date or timestamp value.

Syntax

DATEADD(DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ <date>, BIGINT <delta>, STRING <datepart>)

Parameters

date (required)

The date or timestamp to modify. Supported types: DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ.

In the MaxCompute 1.0 data type edition, a STRING value in the yyyy-mm-dd hh:mi:ss format is implicitly converted to DATETIME. For example, '2025-07-21 00:00:00' is treated as a DATETIME value.

delta (required)

The number of time units to add or subtract. This parameter is of the BIGINT type. A positive value adds the interval. A negative value subtracts it. For example, to add 7 days, set delta to 7. To subtract 3 months, set delta to -3.

In the MaxCompute 1.0 data type edition, STRING and DOUBLE values are implicitly converted to BIGINT.

Note

Adding or subtracting delta can cause a carry or borrow to a more significant time unit. For example, adding 20 months causes the year to increment. Year, month, hour, minute, and second components follow base-10, base-12, base-24, and base-60 systems, respectively.

datepart (required)

The time unit for the interval. This parameter must be a STRING constant. Non-constant values, unsupported formats, or other data types return an error.

Time unit	Accepted values
Year	`year`, `yyyy`
Quarter	`quarter`, `q`
Month	`month`, `mon`, `mm`
Week	`week`
Day	`day`, `dd`
Hour	`hour`, `hh`
Minute	`mi`
Second	`ss`
Millisecond	`ff3`
Microsecond	`ff6`

Return value

Returns a value of the same type as the input date parameter (DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ).

Condition	Result
`date` is not DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ	Error
`date` is NULL	Error
`delta` is NULL	NULL
`datepart` is NULL	NULL

Month-end handling

When datepart is month (or quarter), DATEADD preserves the day value if the resulting month has enough days. If the resulting month has fewer days, the day is set to the last day of that month.

-- January 29 + 1 month: February 2005 has only 28 days, so the result is February 28.
SELECT DATEADD(DATETIME '2005-01-29 00:00:00', 1, 'mm');
-- Result: 2005-02-28 00:00:00

-- March 30 - 1 month: February 2005 has only 28 days, so the result is February 28.
SELECT DATEADD(DATETIME '2005-03-30 00:00:00', -1, 'mm');
-- Result: 2005-02-28 00:00:00

Examples

Effect of each datepart

Each query applies a different datepart to the same base date:

-- Year: add 1 year
SELECT DATEADD(DATETIME '2005-02-28 00:00:00', 1, 'yyyy');
-- Result: 2006-02-28 00:00:00

-- Quarter: add 1 quarter (3 months)
SELECT DATEADD(DATE '2005-02-28', 1, 'quarter');
-- Result: 2005-05-28

-- Month: add 1 month
SELECT DATEADD(DATETIME '2005-02-28 00:00:00', 1, 'mm');
-- Result: 2005-03-28 00:00:00

-- Week: add 2 weeks (14 days)
SELECT DATEADD(DATETIME '2005-02-28 00:00:00', 2, 'week');
-- Result: 2005-03-14 00:00:00

-- Day: add 1 day
SELECT DATEADD(DATETIME '2005-02-28 00:00:00', 1, 'dd');
-- Result: 2005-03-01 00:00:00

-- Hour: subtract 3 hours
SELECT DATEADD(DATETIME '2005-03-30 05:24:00', -3, 'hh');
-- Result: 2005-03-30 02:24:00

-- Minute: subtract 30 minutes
SELECT DATEADD(DATETIME '2005-03-30 05:24:00', -30, 'mi');
-- Result: 2005-03-30 04:54:00

-- Second: subtract 30 seconds
SELECT DATEADD(DATETIME '2005-03-30 05:24:00', -30, 'ss');
-- Result: 2005-03-30 05:23:30

-- Millisecond: add 1000 milliseconds (1 second) to a TIMESTAMP_NTZ value
SELECT DATEADD(TIMESTAMP_NTZ '2005-02-28 00:00:00.123456', 1000, 'ff3');
-- Result: 2005-02-28 00:00:01.123456

-- Microsecond: add 1000 microseconds to a TIMESTAMP value
SELECT DATEADD(TIMESTAMP '2005-02-28 00:00:00.123456', 1000, 'ff6');
-- Result: 2005-02-28 00:00:00.124456

Subtract a day

SELECT DATEADD(DATETIME '2005-02-28 00:00:00', -1, 'dd');
-- Result: 2005-02-27 00:00:00

Month overflow across years

Adding 20 months causes the month to overflow and the year to increment:

SELECT DATEADD(DATETIME '2005-02-28 00:00:00', 20, 'mm');
-- Result: 2006-10-28 00:00:00

Add 1 month to a DATE value

SELECT DATEADD(DATE '2005-02-18', 1, 'mm');
-- Result: 2005-03-18

Preserve sub-second precision with TIMESTAMP

SELECT DATEADD(TIMESTAMP '2005-02-28 00:00:00.123', 14, 'day');
-- Result: 2005-03-14 00:00:00.123

Relative dates with GETDATE

Use DATEADD with GETDATE to calculate dates relative to the current time. For example, DATEADD(GETDATE(), -1, 'dd') returns yesterday's date.

-- Assuming the current time is 2020-11-17 16:31:44
SELECT DATEADD(GETDATE(), -1, 'dd');
-- Result: 2020-11-16 16:31:44

NULL datepart returns NULL

SELECT DATEADD(DATE '2005-02-18', 1, NULL);
-- Result: NULL

Data type edition behavior

MaxCompute 2.0 data type edition

If date is not DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ, explicitly cast it. Otherwise, an error is returned.

SELECT DATEADD(CAST('2005-03-30 00:00:00' AS datetime), -1, 'mm');

-- Result:
-- +------------+
-- | _c0        |
-- +------------+
-- | 2005-02-28 00:00:00 |
-- +------------+

To enable the 2.0 data type edition, submit the following SET command together with the SQL statement:

set odps.sql.type.system.odps2=true;
SELECT DATEADD(TIMESTAMP '2005-02-18 00:00:00', 1, 'mm');
-- Result: 2005-03-18 00:00:00.0

MaxCompute 1.0 data type edition

STRING values in the yyyy-mm-dd hh:mi:ss format are implicitly converted to DATETIME. A STRING that does not match this format returns NULL.

-- Set the data type edition to 1.0.
SET odps.sql.type.system.odps2=false;
SET odps.sql.hive.compatible=false;

-- This STRING does not match the yyyy-mm-dd hh:mi:ss format, so NULL is returned.
SELECT DATEADD('2021-08-27',1,'dd');

-- This STRING matches the format and is implicitly converted to DATETIME.
SELECT DATEADD('2005-02-28 00:00:00', 1000000000, 'ff6');
-- Result: 2005-02-28 00:16:40

Related functions

DATEADD is a date function. For other date calculation and conversion functions, see Date functions.