Snowflake DATEADD: Modify a Date or Time
The DATEADD function in Snowflake is like a time machine for your data. It allows you to add or even subtract time from a specific date or timestamp, making it useful for adjusting dates, or timestamp columns.
What it does
DATEADD lets you manipulate dates by adding a specific amount of time—anything from nanoseconds to years—to a given date, time, or timestamp.
Syntax
Here’s how you write it:
_10DATEADD(`<date_or_time_unit_type>`, `<units>`, `<date_or_time_expr>`)
where:
<date_or_time_unit_type>: The type of time unit you want to add (e.g.,year,month,quarter,week,day,hour,minute,second,millisecond,microsecond, ornanosecond).<units>: How many of these units you want to add (can be positive to add time or negative to subtract time).<date_or_time_expr>: The starting date or time to which you'll add or subtract time.
Common use cases
- Calculating expiration dates: For products or subscriptions that expire after a certain period
_10SELECT_10 DATEADD ('year', 1, purchase_date) AS expiration_date_10FROM_10 subscriptions;
- Analyzing fiscal quarters: To adjust dates to the start of the next fiscal quarter
_10SELECT_10 DATEADD ('quarter', 1, current_date) AS next_quarter_start_10FROM_10 fiscal_calendar;
Examples
Add days to a date
Input
Table: guide_events
| event_name | event_date |
|---|---|
| Arthur's Galactic Travel Start | 1979-10-12 |
| Marvin's Birthday | 1981-01-05 |
Snowflake SQL Query
_10SELECT_10 event_name,_10 event_date,_10 DATEADD ('day', 42, event_date) AS event_date_plus_42_10FROM_10 guide_events;
Output
| event_name | event_date | event_date_plus_42 |
|---|---|---|
| Arthur's Galactic Travel Start | 1979-10-12 | 1979-11-23 |
| Marvin's Birthday | 1981-01-05 | 1981-02-16 |
Add hours to a timestamp
Input
Table: guide_events
| event_name | event_timestamp |
|---|---|
| Arthur's Galactic Travel Start | 1979-10-12 00:00:00.000 |
| Marvin's Birthday | 1981-01-05 03:00:00.000 |
Snowflake SQL Query
_10SELECT_10 event_name,_10 event_timestamp,_10 DATEADD ('hour', 21, event_timestamp) AS event_timestamp_plus_21_10FROM_10 guide_events;
Output
| event_name | event_timestamp | event_timestamp_plus_21 |
|---|---|---|
| Arthur's Galactic Travel Start | 1979-10-12 00:00:00.000 | 1979-10-12 21:00:00.000 |
| Marvin's Birthday | 1981-01-05 03:00:00.000 | 1981-01-06 00:00:00.000 |
