Master Snowflake's DATEDIFF SQL Function in Easy Steps

Example of Snowflake SQL DATEDIFF function

Used to calculate the date differences between two dates in Snowflake. For example:

select *
from sales
where datediff(days, order_date, delivery_date) > 30;

The above SQL, one of the Snowflake Date functions, returns all sales records that took over 30 days from ORDER to DELIVERY date.

What's the DATEDIFF Function useful for?

• Calculate date differences between two dates in Snowflake**.** This includes everything from the number of years, months, weeks, or days between two arbitrary dates or timestamps.

• Equally, you can calculate the time between two timestamps, including hours, minutes, seconds, or even nanoseconds.

---

The syntax for DATEDIFF in Snowflake

The DATEDIFF (or TIMEDIFF) functions uses the following syntax:

datediff ( <UNIT>, <date/time 1>, <date/time 2> );

timediff ( <UNIT>, <date/time 1>, <date/time 2> );

The UNIT determines the unit of time between the dates or times. Values can include:

• Date: Years, Quarters, Months, Weeks or Days

• Time: Hours, Minutes, Seconds, Milliseconds, Microseconds, Nanoseconds

Using DATEDIFF, Calculate the Number of Seconds Between Two Dates

For example, the following Snowflake SQL calculates the number of days and seconds since the start of the year.

select datediff(seconds, to_time('01-Jan-2024','DD-MON-YYYY'), 
                current_time());

Example DATEDIFF function (Days)

The following query lists the warehouses that have been idle for at least 30 days and automatically generates a drop warehouse statement.

show warehouses;
select concat('drop warehouse ', "name",';')
from table(result_scan(last_query_id()))
where datediff(days, "resumed_on", current_date()) > 30;

This could be used (for example) to drop warehouses not used in the past 30 days.

Example DATEDIFF function (Timestamp)

The following query lists the queries executed during the past hour.

select *
from snowflake.account_usage.query_history
where datediff(hours, start_time, current_timestamp) < 1;

---

---