Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

UnitDescriptionExample
secondAdd secondsDATEADD(second, 30, '2024-06-01 12:00:00')
minuteAdd minutesDATEADD(minute, 15, '2024-06-01 12:00:00')
hourAdd hoursDATEADD(hour, 3, '2024-06-01 12:00:00')
dayAdd daysDATEADD(day, 10, '2024-06-01')
monthAdd monthsDATEADD(month, 2, '2024-06-01')
yearAdd yearsDATEADD(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.