Learn DATEDIFF SQL Function syntax

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.

Snowflake Training by an Expert.

Click in the image below for more detail

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;

Snowflake Training from an Snowflake Superhero.

Click on the image below for more information.

Master Snowflake's DATEDIFF SQL Function in Easy Steps