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, 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


_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

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free