How to Use DATEADD in Snowflake: Syntax and Examples
Use the
DATEADD function in Snowflake to add a specified interval to a date or timestamp. The syntax is DATEADD(unit, value, date_or_time), where unit is the part of the date to add to, value is how much to add, and date_or_time is the starting date or timestamp.Syntax
The DATEADD function adds a specified amount of time to a date or timestamp.
- unit: The part of the date to add to, such as day, month, year, hour, minute, or second.
- value: The number of units to add. Can be positive (to add) or negative (to subtract).
- date_or_time: The starting date or timestamp to which the interval is added.
sql
DATEADD(unit, value, date_or_time)Example
This example adds 10 days to the date '2024-06-01'. It shows how DATEADD changes the date by adding the specified interval.
sql
SELECT DATEADD(day, 10, '2024-06-01') AS new_date;
Output
NEW_DATE
2024-06-11
Common Pitfalls
Common mistakes include:
- Using incorrect unit names (e.g., 'days' instead of 'day').
- Passing a string that is not a valid date or timestamp.
- Confusing the order of arguments.
Always use singular unit names and valid date formats.
sql
/* Wrong: using plural unit */ -- SELECT DATEADD(days, 5, '2024-06-01'); -- This will cause an error /* Right: use singular unit */ SELECT DATEADD(day, 5, '2024-06-01') AS correct_date;
Output
CORRECT_DATE
2024-06-06
Quick Reference
| Unit | Description | Example |
|---|---|---|
| second | Add seconds | DATEADD(second, 30, '2024-06-01 12:00:00') |
| minute | Add minutes | DATEADD(minute, 15, '2024-06-01 12:00:00') |
| hour | Add hours | DATEADD(hour, 3, '2024-06-01 12:00:00') |
| day | Add days | DATEADD(day, 10, '2024-06-01') |
| month | Add months | DATEADD(month, 2, '2024-06-01') |
| year | Add years | DATEADD(year, 1, '2024-06-01') |
Key Takeaways
Use singular unit names like 'day', 'month', or 'year' in DATEADD.
The value can be positive to add or negative to subtract time.
DATEADD works with both dates and timestamps.
Always provide a valid date or timestamp as the third argument.
Common errors come from wrong unit names or invalid date formats.
