Snowflake
Calculate Difference Between Dates/Times

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:


_10
DATEDIFF(`<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, or nanosecond).
  • <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

_10
SELECT
_10
DATEDIFF ('year', birth_date, CURRENT_DATE()) AS age
_10
FROM
_10
people;

  • Subscription validity: To check how many months are left before a subscription expires

_10
SELECT
_10
DATEDIFF ('month', CURRENT_DATE(), subscription_end_date) AS months_until_expiration
_10
FROM
_10
subscriptions;

Examples

Calculate the difference between two dates in years

Input

Table: galactic_events

event_namestart_dateend_date
Travel with Ford Prefect1978-10-121985-10-12
Marvin's Waiting1981-01-011982-01-01
Earth's Destruction1980-12-121982-12-12

Snowflake SQL Query


_10
SELECT
_10
event_name,
_10
start_date,
_10
end_date,
_10
DATEDIFF ('year', start_date, end_date) AS diff_years
_10
FROM
_10
galactic_events;

Output

event_namestart_dateend_datediff_years
Travel with Ford Prefect1978-10-121985-10-127
Marvin's Waiting1981-01-011982-01-011
Earth's Destruction1980-12-121982-12-122

Calculate the difference between two timestamps in hours

Input

Table: galactic_events

event_namestart_timestampend_timestamp
Travel with Ford Prefect1978-10-12 08:00:00.0001985-10-12 20:00:00.000
Marvin's Waiting1981-01-01 09:00:00.0001982-01-01 18:00:00.000
Earth's Destruction1980-12-12 07:30:00.0001982-12-12 19:45:00.000

Snowflake SQL Query


_10
SELECT
_10
event_name,
_10
start_timestamp,
_10
end_timestamp,
_10
DATEDIFF ('hour', start_timestamp, end_timestamp) AS diff_hours
_10
FROM
_10
galactic_events;

Output

event_namestart_timestampend_timestampdiff_hours
Travel with Ford Prefect1985-10-12 15:30:00.0001985-10-12 20:00:00.0005
Marvin's Waiting1981-12-31 23:00:00.0001982-01-01 18:00:00.00019
Earth's Destruction1982-12-12 18:00:00.0001982-12-12 19:45:00.0001

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_namestart_dateend_date
Travel with Ford Prefect1978-10-121985-10-12
Marvin's Waiting1981-01-011982-01-01
Earth's Destruction1980-12-121982-12-12

Snowflake SQL Query


_10
SELECT
_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
_10
FROM
_10
galactic_events;

Output

event_namestart_dateend_datediff_daysdiff_days_minus
Travel with Ford Prefect1978-10-121985-10-1225572557
Marvin's Waiting1981-01-011982-01-01365365
Earth's Destruction1980-12-121982-12-12730730
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started