How to Use DATEDIFF in Snowflake: Syntax and Examples
In Snowflake, use the
DATEDIFF function to find the difference between two dates by specifying the date part (like day, month, year) and the two dates. The syntax is DATEDIFF(date_part, start_date, end_date), which returns the count of the specified date part between the dates.Syntax
The DATEDIFF function calculates the difference between two dates based on the specified date part.
- date_part: The unit to measure difference (e.g., day, month, year).
- start_date: The first date.
- end_date: The second date.
The result is the number of date_part units from start_date to end_date.
sql
DATEDIFF(date_part, start_date, end_date)Example
This example shows how to calculate the number of days between two dates using DATEDIFF. It returns 5 because there are 5 days from January 1, 2024, to January 6, 2024.
sql
SELECT DATEDIFF(day, '2024-01-01', '2024-01-06') AS days_difference;
Output
DAYS_DIFFERENCE
5
Common Pitfalls
Common mistakes include:
- Using the wrong order of dates:
DATEDIFFcounts fromstart_datetoend_date, so reversing them changes the sign. - Using unsupported date parts or misspelling them.
- Passing non-date values which cause errors.
Always ensure dates are in a valid format and the date part is correct.
sql
/* Wrong: start_date after end_date returns negative */ SELECT DATEDIFF(day, '2024-01-06', '2024-01-01') AS diff_wrong; /* Correct: start_date before end_date */ SELECT DATEDIFF(day, '2024-01-01', '2024-01-06') AS diff_correct;
Output
DIFF_WRONG
-5
DIFF_CORRECT
5
Quick Reference
| Date Part | Description | Example |
|---|---|---|
| day | Difference in days | DATEDIFF(day, '2024-01-01', '2024-01-06') returns 5 |
| month | Difference in months | DATEDIFF(month, '2024-01-01', '2024-03-01') returns 2 |
| year | Difference in years | DATEDIFF(year, '2020-01-01', '2024-01-01') returns 4 |
| hour | Difference in hours | DATEDIFF(hour, '2024-01-01 10:00', '2024-01-01 15:00') returns 5 |
| minute | Difference in minutes | DATEDIFF(minute, '10:00', '10:30') returns 30 |
Key Takeaways
Use DATEDIFF(date_part, start_date, end_date) to calculate the difference between two dates in Snowflake.
The order of dates matters: the result is positive if end_date is after start_date, negative otherwise.
Common date parts include day, month, year, hour, and minute.
Ensure date inputs are valid and date parts are spelled correctly to avoid errors.
DATEDIFF returns an integer count of the specified date part between the two dates.
