1. DATEADD Function in Snowflake
DATEADD Function in Snowflake calculates a date or timestamp value by adding a specified interval to a given date or timestamp. The function is useful for dynamically adjusting dates in reporting, scheduling, and data analysis tasks.
Snowflake also supports TIMEADD and TIMESTAMPADD functions to calculate date or timestamp values from a specified date value which work exactly like the DATEADD function. It is similar to the ADD_TO_DATE function available in Informatica Cloud Data Integration.
2. Syntax of Snowflake DATEADD Function
The following is the syntax of the DATEADD Function in Snowflake.
DATEADD( Date_or_Time_Part, Interval_Value, Date_or_Time_Value )2.1. Date_or_Time_Part
It represents the unit of time that needs to be added or subtracted from a date value. The following are the supported date/time part values in the DATEADD function.
| Type | Supported Date_or_Time_Part Values |
| Date value | YEAR, QUARTER, MONTH, WEEK, DAY |
| Time value | HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND |
2.2. Interval_Value
It represents the number of units of time that should be added or subtracted from a date value.
For example, if the unit of time is day, to add two days, specify 2. To subtract two days, specify -2.
2.3. Date_or_Time_Value
It represents the date, time, or timestamp value from which you want to calculate a new date, time, or timestamp by adding or subtracting time intervals.
3. How to Add or Subtract Time Intervals from Dates using DATEADD Function in Snowflake?
The following example demonstrates adding and subtracting time intervals from a specified Date or Timestamp value using the DATEADD function in Snowflake.
-- Calculate CURRENT and PREVIOUS Year Values
SELECT
'2024-01-01' AS CURRENT_YEAR,
DATEADD(YEAR,-1,'2024-01-01') AS PREVIOUS_YEAR,
DATEADD(YEAR, 1,'2024-01-01') AS NEXT_YEAR
;
+--------------+---------------------------+---------------------------+
| CURRENT_YEAR | PREVIOUS_YEAR | NEXT_YEAR |
+--------------+---------------------------+---------------------------+
| 2024-01-01 | 2023-01-01 00:00:00.000 | 2025-01-01 00:00:00.000 |
+--------------+---------------------------+---------------------------+-- Calculate CURRENT and PREVIOUS Day Values
SELECT
'2024-01-01' AS CURRENT_DAY,
DATEADD(DAY,-1,'2024-01-01') AS PREVIOUS_DAY,
DATEADD(DAY, 1,'2024-01-01') AS NEXT_DAY
;
+--------------+---------------------------+---------------------------+
| CURRENT_DAY | PREVIOUS_DAY | NEXT_DAY |
+--------------+---------------------------+---------------------------+
| 2024-01-01 | 2023-12-31 00:00:00.000 | 2024-01-02 00:00:00.000 |
+--------------+---------------------------+---------------------------+-- Calculate CURRENT and PREVIOUS Hour Values
SELECT
'2024-01-01 01:00:00' AS CURRENT_HOUR,
DATEADD(HOUR,-1,'2024-01-01 01:00:00') AS PREVIOUS_HOUR,
DATEADD(HOUR, 1,'2024-01-01 01:00:00') AS NEXT_HOUR
;
+---------------------+---------------------------+--------------------------+
| CURRENT_YEAR | PREVIOUS_HOUR | NEXT_HOUR |
+---------------------+---------------------------+--------------------------+
| 2024-01-01 01:00:00 | 2024-01-01 00:00:00.000 | 2024-01-01 02:00:00.000 |
+---------------------+---------------------------+--------------------------+4. Use Cases of the DATEADD Function in Snowflake
The following are some of the use cases of DATEADD Function in Snowflake
4.1. Rolling Date Windows
The following example retrieves a distinct list of files from the specified stage (@my_stage) that have been modified within the last seven days using the DATEADD function.
SELECT DISTINCT
METADATA$FILENAME, METADATA$FILE_LAST_MODIFIED
FROM @my_stage
WHERE METADATA$FILE_LAST_MODIFIED BETWEEN DATEADD(DAY,-7, CURRENT_DATE) AND CURRENT_DATE
;
+---------------------------------+-----------------------------+
| METADATA$FILENAME | METADATA$FILE_LAST_MODIFIED |
+---------------------------------+-----------------------------+
| sales_20240101.csv | 2024-01-01 12:30:00.000 |
| sales_20241231.csv | 2023-12-31 10:45:00.000 |
| sales_20241229.csv | 2023-12-29 08:15:00.000 |
+---------------------------------+-----------------------------+4.2. Time Zone Adjustments
The following example converts time in UTC timezone to IST timezone which is five and half hours ahead of UTC time using DATEADD function.
SELECT
SYSDATE() UTC_TIME,
DATEADD(MINUTE,330,SYSDATE()) IST_TIME
;
+----------------------------+----------------------------+
| UTC_TIME | IST_TIME |
+----------------------------+----------------------------+
| 2024-10-27 14:45:14.521 | 2024-10-27 20:15:14.521 |
+----------------------------+----------------------------+4.3. Setting Expiration and Reminder Dates
The following example calculates the expiration dates of passwords for users in the table based on their last reset dates using the DATEADD function.
SELECT
USER_ID,
LAST_RESET_DATE,
DATEADD(DAY, 90, LAST_RESET_DATE) AS EXPIRATION_DATE
FROM USERS;
+---------+---------------------+---------------------+
| USER_ID | LAST_RESET_DATE | EXPIRATION_DATE |
+---------+---------------------+---------------------+
| 1 | 2024-08-01 10:00:00 | 2024-10-30 10:00:00 |
| 2 | 2024-08-15 15:30:00 | 2024-11-13 15:30:00 |
| 3 | 2024-09-01 12:00:00 | 2024-11-30 12:00:00 |
| 4 | 2024-09-10 08:45:00 | 2024-12-09 08:45:00 |
| 5 | 2024-10-01 09:15:00 | 2024-12-30 09:15:00 |
+---------+---------------------+---------------------+4.4. Historical Data Analysis
The following example compares current and previous year sales for a specific day using DATEADD function.
SELECT
a.SALES_DATE,
SUM(a.SALES_AMOUNT) AS CURRENT_DAY_SALES,
SUM(b.SALES_AMOUNT) AS PREVIOUS_YEAR_SALES
FROM SALES a LEFT JOIN SALES b
ON DATEADD(YEAR, -1, a.SALES_DATE) = b.SALES_DATE
WHERE YEAR(a.SALES_DATE) = 2024
GROUP BY a.SALES_DATE
;
-- Source Data
+------------+--------------+
| SALES_DATE | SALES_AMOUNT |
+------------+--------------+
| 2024-10-27 | 1500.00 |
| 2024-10-26 | 1800.00 |
| 2024-10-25 | 1600.00 |
| 2023-10-27 | 1200.00 |
| 2023-10-26 | 1700.00 |
| 2023-10-25 | 1100.00 |
+------------+--------------+
-- Target Data
+--------------+---------------------+-----------------------+
| SALES_DATE | CURRENT_DAY_SALES | PREVIOUS_YEAR_SALES |
+--------------+---------------------+-----------------------+
| 2024-10-27 | 1500.00 | 1200.00 |
| 2024-10-26 | 1800.00 | 1700.00 |
| 2024-10-25 | 1600.00 | 1100.00 |
+--------------+---------------------+-----------------------+Subscribe to our Newsletter !!
Related Articles:
