DATE_ADD

​

Syntax

​

Days (scalar)

date_add(date, days)

​

Units (scalar)

date_add(unit, amount, value)

​

Dateadd units (scalar)

dateadd(unit, amount, value)

• DATEADD uses a unit keyword such as DAY; do not quote it as a STRING literal.

​

Arguments

​

Days

​

Units

​

Dateadd units

​

Returns

• Type: DATE for the two-argument form; TIMESTAMP for the three-argument form, including when value is DATE.
• NULL behavior: NULL inputs return NULL. Values outside the supported range can raise an error.

​

Days

• Type: DATE
• NULL behavior: NULL value or amount yields NULL; arithmetic overflow errors.

​

Properties

​

Units

• Input unit: Signed calendar day count or specified temporal units
• Output unit: Temporal value

​

Usage notes

• Two arguments always operate in the DATE domain.
• In the three-argument form, numeric amounts are converted to BIGINT before addition. Fractional amounts are truncated toward zero: 1.9 adds one unit, and -1.9 subtracts one unit.
• DATE input in the three-argument form becomes a midnight TIMESTAMP, so hours and smaller units can change its time of day.
• TIME inputs are not accepted by these calling forms. Use a DATE or TIMESTAMP value.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

​

Examples

​

Basic example

SELECT date_add(DATE '2024-01-15',5) AS result;

​

Alias calling form: DATEADD

SELECT dateadd(DAY,1,TIMESTAMP '2024-01-01 10:30:00') AS result;

WITH t(amount) AS (VALUES (CAST(1.9 AS DOUBLE)), (CAST(-1.9 AS DOUBLE))) SELECT amount, dateadd(DAY, amount, DATE '2024-01-10') AS result FROM t ORDER BY amount;

​

Limitations

• The three-argument form returns TIMESTAMP, including for a DATE input.
• Amounts must fit BIGINT after conversion, and the resulting date or timestamp must remain within its supported range. Do not rely on NULL being returned for overflow.