DATEADD function in Snowflake - SQL Syntax and Examples

DATEADD Description

DATEADD function Syntax

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

DATEADD function Examples

Add years to a date:

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

Output:

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

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');

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;

Output:

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

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;

Output:

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

Add a month to a date in a month with the same or more days than the resulting month. E.g. 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;

Output:

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

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;

Output:

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

DATEADD function Usage

• 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.

DATEADD function 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 .

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).

DATEADD function Return Value

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.

Convert your code online to Snowflake