How to Use Date Functions in Snowflake: Syntax and Examples
In Snowflake, you use
DATE functions like CURRENT_DATE(), DATEADD(), and DATEDIFF() to work with dates. These functions help you get the current date, add or subtract time, and calculate differences between dates easily.Syntax
Snowflake provides several date functions with simple syntax:
CURRENT_DATE(): Returns today's date.DATEADD(unit, value, date): Adds or subtracts a time unit (like day, month) to a date.DATEDIFF(unit, start_date, end_date): Calculates the difference between two dates in the specified unit.
sql
SELECT CURRENT_DATE() AS today_date; SELECT DATEADD(day, 5, '2024-06-01') AS new_date; SELECT DATEDIFF(day, '2024-06-01', '2024-06-10') AS days_diff;
Output
TODAY_DATE
2024-06-15
NEW_DATE
2024-06-06
DAYS_DIFF
9
Example
This example shows how to get the current date, add 10 days, and find the difference in days between two dates.
sql
SELECT CURRENT_DATE() AS current_date, DATEADD(day, 10, CURRENT_DATE()) AS date_plus_10, DATEDIFF(day, '2024-06-01', CURRENT_DATE()) AS days_since_june_1;
Output
CURRENT_DATE | DATE_PLUS_10 | DAYS_SINCE_JUNE_1
2024-06-15 | 2024-06-25 | 14
Common Pitfalls
Common mistakes when using date functions in Snowflake include:
- Using string dates without quotes or in wrong format (Snowflake expects 'YYYY-MM-DD').
- Mixing up the order of arguments in
DATEDIFF()(start date first, end date second). - Forgetting that
DATEADD()can subtract by using negative values.
sql
/* Wrong: missing quotes around date string */ -- SELECT DATEADD(day, 5, 20240601); /* Right: use quotes and correct format */ SELECT DATEADD(day, 5, '2024-06-01'); /* Wrong: swapped dates in DATEDIFF */ -- SELECT DATEDIFF(day, '2024-06-10', '2024-06-01'); /* Right: start date first, end date second */ SELECT DATEDIFF(day, '2024-06-01', '2024-06-10');
Output
NEW_DATE
2024-06-06
DAYS_DIFF
9
Quick Reference
| Function | Purpose | Example |
|---|---|---|
| CURRENT_DATE() | Returns today's date | SELECT CURRENT_DATE(); |
| DATEADD(unit, value, date) | Adds or subtracts time | SELECT DATEADD(day, 3, '2024-06-01'); |
| DATEDIFF(unit, start_date, end_date) | Calculates difference between dates | SELECT DATEDIFF(day, '2024-06-01', '2024-06-10'); |
| DATE_TRUNC(unit, date) | Truncates date to unit (e.g., month) | SELECT DATE_TRUNC(month, '2024-06-15'); |
Key Takeaways
Use
CURRENT_DATE() to get today's date in Snowflake.Add or subtract time with
DATEADD() by specifying unit and value.Calculate days or other units between dates using
DATEDIFF() with correct argument order.Always use date strings in 'YYYY-MM-DD' format and enclose them in single quotes.
Remember
DATE_TRUNC() to round dates to a specific unit like month or year.