Snowflake DATEDIFF: 4 Quick Reference Examples

As a powerful engine for large-scale analytics, Snowflake is often used to process large datasets containing time-series data. Oftentimes, Snowflake users will need to calculate the difference in time between two dates or timestamps. Luckily, Snowflake makes this super easy with its DATEDIFF function.

Let’s dive in.

DATEDIFF Function Syntax

As shown in the image above, there are three inputs to the DATEDIFF function:

DATEDIFF( <unit>, <date/timestamp #1>, <date/timestamp #2>)

**unit****: **the unit of time to calculate the difference in (eg. 'day', 'month', 'hour', etc.)
**date/timestamp #1****: **the first date/timestamp
**date/timestamp #2****: **the second date/timestamp

Example Usage

When you call datediff('day', date'2024-04-01', date'2024-05-11'), Snowflake will evaluate that as **the number of days between 2024-05-11 and 2024-04-01 **(40).

 select datediff('day', date'2024-04-01', date'2024-05-11')
 -- evaluated as: 2024-05-11 - 2024-04-01

Supported Date Parts

When comparing two dates, the following values are accepted for the unit parameter:

year
quarter
month
week
date

Supported Time Parts

When comparing two timestamps, the following values are accepted for the unit parameter:

hour
minute
second
millisecond
microsecond
nanosecond

Now that we’ve covered the function syntax and required parameters, let’s look at some examples.

Number of days between two dates

To calculate the difference between two dates, you’ll supply day as the unit followed by the two dates datediff('day', date'2024-04-01', date'2024-05-11').

select datediff('day', date'2024-04-01', date'2024-05-11')
-- Output: 40

-- Quotes around the unit are not required! 'day' and day are both accepted
select datediff(day, date'2024-04-01', date'2024-05-11')
-- Output: 40

This effectively gets evaluated as 2024-05-11 - 2024-04-01.

Note, you can also just use the - sign directly in the SQL rather than passing the dates into the DATEDIFF function:

select date'2024-05-11' - date'2024-04-01'
-- Output: 40

Impact of date ordering

DATEDIFF always calculates the difference as date 2 - date 1. If we swap the example above, we’ll get a negative:

select datediff('day', date'2024-05-11', date'2024-04-01')
-- Output: -40

Number of months between two dates

To calculate the number of months between two dates, we just need to supply month as the unit:

select datediff('month', date'2024-04-01', date'2024-05-11')
-- Output: 1

Note how Snowflake still outputs 1 month between the two dates, even though they are only 11 days apart:

select datediff('month', date'2024-04-30', date'2024-05-11')
-- Output: 1

If two dates fall in the same month, the output will be 0:

select datediff('month', date'2024-05-01', date'2024-05-11')
-- Output: 0

Number of hours between two timestamps

DATEDIFF can also compare two timestamps instead of dates. Here’s an example showing the number of hours between two timestamps:

select datediff('hour', timestamp'2024-04-11 12:00:00', timestamp'2024-04-11 14:00:00')
--Output: 2

Number of seconds between two timestamps

When working with timestamps, the difference calculation can be done more granularly. As per the accepted time part options listed above, you can pass in second to calculate the number of seconds between two timestamps:

select datediff('second', timestamp'2024-04-11 12:00:00', timestamp'2024-04-11 14:00:00')
-- Output: 7200

DATEDIFF Rounding

DATEDIFF does not do any rounding.

If we calculate the number of days between April 1 and May 30, it is 59:

select datediff(day, date'2024-04-01', date'2024-05-30')
-- Output: 59

When we calculate the number of months between these same dates, the output is 1, even though they are closer to two months apart:

select datediff(month, date'2024-04-01', date'2024-05-30')
-- Output: 1