Snowflake DATEDIFF: Calculate the Difference Between Dates or Times
You can think of DATEDIFF as a way for measuring the time distance between two dates, similar to using a ruler to measure the space between two points. This function tells you how much time has passed from one date to another, which can be helpful for tracking durations, deadlines, or age.
What it does
DATEDIFF calculates the difference between two dates, times, or timestamps. You specify the unit of time for the measurement (like days, months, or years), and it tells you how many of those units fit into the span between two dates.
Syntax
Here’s how you use it:
_10DATEDIFF(`<date_or_time_part>`, `<start_date_or_time_expr1>`, `<end_date_or_time_expr2>`)
where:
<date_or_time_part>The unit of time you want to count (e.g.,year,month,quarter,week,day,hour,minute,second,millisecond,microsecond, ornanosecond).<start_date_or_time_expr1>: The starting date or time.<end_date_or_time_expr2>: The ending date or time.
Quick difference in days
If you just want to know the difference in days, you can subtract one date from another:
_10<end_date_expr2> - <start_date_expr1>
This calculation will give you the number of days between the two dates.
Common use cases
- Age calculation: To calculate a person's age in years
_10SELECT_10 DATEDIFF ('year', birth_date, CURRENT_DATE()) AS age_10FROM_10 people;
- Subscription validity: To check how many months are left before a subscription expires
_10SELECT_10 DATEDIFF ('month', CURRENT_DATE(), subscription_end_date) AS months_until_expiration_10FROM_10 subscriptions;
Examples
Calculate the difference between two dates in years
Input
Table: galactic_events
| event_name | start_date | end_date |
|---|---|---|
| Travel with Ford Prefect | 1978-10-12 | 1985-10-12 |
| Marvin's Waiting | 1981-01-01 | 1982-01-01 |
| Earth's Destruction | 1980-12-12 | 1982-12-12 |
Snowflake SQL Query
_10SELECT_10 event_name,_10 start_date,_10 end_date,_10 DATEDIFF ('year', start_date, end_date) AS diff_years_10FROM_10 galactic_events;
Output
| event_name | start_date | end_date | diff_years |
|---|---|---|---|
| Travel with Ford Prefect | 1978-10-12 | 1985-10-12 | 7 |
| Marvin's Waiting | 1981-01-01 | 1982-01-01 | 1 |
| Earth's Destruction | 1980-12-12 | 1982-12-12 | 2 |
Calculate the difference between two timestamps in hours
Input
Table: galactic_events
| event_name | start_timestamp | end_timestamp |
|---|---|---|
| Travel with Ford Prefect | 1978-10-12 08:00:00.000 | 1985-10-12 20:00:00.000 |
| Marvin's Waiting | 1981-01-01 09:00:00.000 | 1982-01-01 18:00:00.000 |
| Earth's Destruction | 1980-12-12 07:30:00.000 | 1982-12-12 19:45:00.000 |
Snowflake SQL Query
_10SELECT_10 event_name,_10 start_timestamp,_10 end_timestamp,_10 DATEDIFF ('hour', start_timestamp, end_timestamp) AS diff_hours_10FROM_10 galactic_events;
Output
| event_name | start_timestamp | end_timestamp | diff_hours |
|---|---|---|---|
| Travel with Ford Prefect | 1985-10-12 15:30:00.000 | 1985-10-12 20:00:00.000 | 5 |
| Marvin's Waiting | 1981-12-31 23:00:00.000 | 1982-01-01 18:00:00.000 | 19 |
| Earth's Destruction | 1982-12-12 18:00:00.000 | 1982-12-12 19:45:00.000 | 1 |
Calculate the difference between two dates in days
If we want to return the difference in days between two date fields, we can use either the DATEDIFF function or the minus sign (-). For the minus sign, the first argument is the end date, and the second is be the start date.
Input
Table: galactic_events
| event_name | start_date | end_date |
|---|---|---|
| Travel with Ford Prefect | 1978-10-12 | 1985-10-12 |
| Marvin's Waiting | 1981-01-01 | 1982-01-01 |
| Earth's Destruction | 1980-12-12 | 1982-12-12 |
Snowflake SQL Query
_10SELECT_10 event_name,_10 start_date,_10 end_date,_10 DATEDIFF ('day', start_date, end_date) AS diff_days,_10 end_date - start_date as diff_days_minus_10FROM_10 galactic_events;
Output
| event_name | start_date | end_date | diff_days | diff_days_minus |
|---|---|---|---|---|
| Travel with Ford Prefect | 1978-10-12 | 1985-10-12 | 2557 | 2557 |
| Marvin's Waiting | 1981-01-01 | 1982-01-01 | 365 | 365 |
| Earth's Destruction | 1980-12-12 | 1982-12-12 | 730 | 730 |
