DATEADD¶

Syntax¶

DATEADD( <date_or_time_part>, <value>, <date_or_time_expr> )

Arguments¶

date_or_time_part

This indicates the units of time that you want to add. For example if you want to add 2 days, then this will be DAY. This unit of measure must be one of the values listed in Supported date and time parts.

value

This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.

date_or_time_expr

date_or_time_expr must evaluate to a date, time, or timestamp. This is the date, time, or timestamp to which you want to add. For example, if you want to add 2 days to August 1, 2018, then this will be '2018-08-01'::DATE.

If the data type is TIME, then the date_or_time_part must be in units of hours or smaller, not days or bigger.

If the input data type is DATE, and the date_or_time_part is hours or smaller, the input value will not be rejected, but instead will be treated as a TIMESTAMP with hours, minutes, seconds, and fractions of a second all initially set to 0 (e.g. midnight on the specified date).

Returns¶

If date_or_time_expr is a time:

• The return data type is a time.

If date_or_time_expr is a timestamp:

• The return data type is a timestamp.

If date_or_time_expr is a date:

• If date_or_time_part is day or larger (e.g. month, year), the function returns a DATE value.

• If date_or_time_part is smaller than a day (e.g. hour, minute, second), the function returns a TIMESTAMP_NTZ value, with 00:00:00.000 as the starting time for the date.

Usage notes¶

• When date_or_time_part is year, quarter, or month (or any of their variations), if the result month has fewer days than the original day of the month, the result day of the month might be different from the original day.

Examples¶

Add years to a date:

SELECT TO_DATE('2013-05-08') AS v1, DATEADD(year, 2, TO_DATE('2013-05-08')) AS v;

Copy

Output:

+------------+------------+
| V1         | V          |
|------------+------------|
| 2013-05-08 | 2015-05-08 |
+------------+------------+

Copy

Add 2 years and 2 hours to a date:

Initial setup of this example and some subsequent examples:

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9';
CREATE TABLE datetest (d date);
INSERT INTO datetest VALUES ('2013-04-05');

Copy

Query to add 2 years and 2 hours to a date:

SELECT d AS "DATE", dateadd(year, 2, d) AS add_2_years, 
    dateadd(hour, 2, d) AS add_2_hours
  FROM datetest;

Copy

Output:

+------------+-------------+-------------------------+
| DATE       | ADD_2_YEARS | ADD_2_HOURS             |
|------------+-------------+-------------------------|
| 2013-04-05 | 2015-04-05  | 2013-04-05 02:00:00.000 |
+------------+-------------+-------------------------+

Copy

Add hours to a timestamp:

SELECT TO_TIMESTAMP_LTZ('2013-05-08 11:22:33.444') AS v1,
  DATEADD(HOUR, 2, TO_TIMESTAMP_LTZ('2013-05-08 11:22:33.444')) AS v;

Copy

Output:

+-------------------------------+-------------------------------+
| V1                            | V                             |
|-------------------------------+-------------------------------|
| 2013-05-08 11:22:33.444000000 | 2013-05-08 13:22:33.444000000 |
+-------------------------------+-------------------------------+

Copy

Add a month to a date in a month with the same or more days than the resulting month. For example, if today is January 31, adding a month should not give us “February 31”.

SELECT DATEADD(MONTH, 1, '2000-01-31'::DATE) AS DIFFERENT_DAY;

Copy

Output:

+---------------+
| DIFFERENT_DAY |
|---------------|
| 2000-02-29    |
+---------------+

Copy

Add a month to a date in a month with fewer days than the resulting month. Adding a month to February 29 should give us March 29, which is safe.

SELECT DATEADD(MONTH, 1, '2000-02-29'::DATE) AS RESULT;

Copy

Output:

+------------+
| RESULT     |
|------------|
| 2000-03-29 |
+------------+

Copy