DATEDIFF() function in Snowflake ?

Explained: DATEDIFF() function in Snowflake?

by

Are you looking to find how to use the DATEDIFF() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the DATEDIFF function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use DATEDIFF() function. I will explain the DATEDIFF() function by taking a practical example. So, don’t waste time let’s start a step-by-step guide to understanding what is the DATEDIFF() expression.

What is DATEDIFF() Function?

DATEDIFF() function helps to get the difference between dates, timestamps, or time. For example, if we want to calculate the days present between January 10th and March 5th, then we can make use of this function.

How can we use DATEDIFF() Function?

DATEDIFF() function is used to subtract two dates, times, or timestamps based on the date or time part requested. The function returns the result of subtracting the second argument from the third argument.

Syntax for DATEDIFF function in Snowflake

-- Syntax : 

DATEDIFF ( Date_or_time_part, Expression-1, Expression-2)

DATEDIFF() Argument Details:

Date_or_time_partthe unit value ( Mandatory )
Expression-1Date or Time Expression ( Mandatory )
Expression-2Date or Time Expression ( Mandatory )
  • The Minus sign can also be used to subtract the dates.
  • Here the values can be negative ( example : -12 days ).
  • This function supports years, quarters, months, weeks, days, hours, minutes and seconds.
  • The units are used to calculate the difference is a Date part ( year, month, date ) or Time part (hours, minute, second) only relevant parts will be used.
  • Hour uses only the hour from the time/timestamp.
    • Minute uses the hour and minutes from the time/timestamp.
    • Second uses the entire time/timestamp.
    • Year uses only the year from the date.
    • Month uses the month and year from the date.
    • Day uses the entire date.

Examples :

Example to calculate the difference between years using datediff()?

select datediff(year, '2009-04-09','2013-10-08') as diff_years;

+------------+
| DIFF_YEARS |
|------------|
|          4 |
+------------+

Example to calculate the difference between hours using datediff()?

select datediff(hour, '2013-05-08T23:39:20.123-07:00'::timestamp,
                                  '2013-05-09T23:39:20.123-07:00'::timestamp) as diff_hours;

+------------+
| DIFF_HOURS |
|------------|
|         24 |
+------------+

Full Example of DATEDIFF() function in Snowflake.

-- Create a Table

create or replace TABLE EMP1 (
	ID NUMBER(38,0),
	NAME VARCHAR(30),
	INFO VARCHAR(20),
	AMOUNT NUMBER(38,0),
	JOIN_DATE DATE
);

-- Insert Data

insert into emp(id,name,amount,join_date) values 
(1,'Ram',2000',2018-12-10'),
(2,'RAJU',2500, '2018-05-21'),
(3,'Sameer',1500, '2018-07-15'),
(4,'Sai Kumar',250, '2018-01-17'),
(5,'Naveen Sai',1000, '2018-10-21'),
(6,'Seshi Kumar',13500, '2018-05-25'),
(7,'Rajiv Ram',18000, '2018-03-12'),
(8,NULL,NULL,NULL)

-- Select the Table Data

select * from demodb.public.emp1;
+----+-------------+----------+--------+------------+
| ID | NAME        | INFO     | AMOUNT | JOIN_DATE  |
|----+-------------+----------+--------+------------|
|  1 | Ram         | NOT-NULL |   2000 | 2018-12-10 |
|  2 | RAJU        | NOT-NULL |   2500 | 2018-05-21 |
|  3 | Sameer      | NOT-NULL |   1500 | 2018-07-15 |
|  4 | Sai Kumar   | NOT-NULL |    250 | 2018-01-17 |
|  5 | Naveen Sai  | NOT-NULL |   1000 | 2018-10-21 |
|  6 | Seshi Kumar | NOT-NULL |  13500 | 2018-05-25 |
|  7 | Rajiv Ram   | NOT-NULL |  18000 | 2018-03-12 |
|  8 | NULL        | NOT-NULL |   NULL | NULL       |
+----+-------------+----------+--------+------------+

-- Apply DateDiff to the Table Data to know total days from the date of joining

select id,join_date,current_date(),
datediff(day,join_date,current_date()) Total_Days 
from demodb.public.emp1;

+----+------------+----------------+------------+
| ID | JOIN_DATE  | CURRENT_DATE() | TOTAL_DAYS |
|----+------------+----------------+------------|
|  1 | 2018-12-10 | 2022-02-23     |       1171 |
|  2 | 2018-05-21 | 2022-02-23     |       1374 |
|  3 | 2018-07-15 | 2022-02-23     |       1319 |
|  4 | 2018-01-17 | 2022-02-23     |       1498 |
|  5 | 2018-10-21 | 2022-02-23     |       1221 |
|  6 | 2018-05-25 | 2022-02-23     |       1370 |
|  7 | 2018-03-12 | 2022-02-23     |       1444 |
|  8 | NULL       | 2022-02-23     |       NULL |
+----+------------+----------------+------------+

-- Apply DateDiff to the Table Data to know total months from the date of joining

select id,join_date,current_date(),datediff(MONTH,join_date,
current_date()) Total_month from demodb.public.emp1;

+----+------------+----------------+-------------+
| ID | JOIN_DATE  | CURRENT_DATE() | TOTAL_MONTH |
|----+------------+----------------+-------------|
|  1 | 2018-12-10 | 2022-02-23     |          38 |
|  2 | 2018-05-21 | 2022-02-23     |          45 |
|  3 | 2018-07-15 | 2022-02-23     |          43 |
|  4 | 2018-01-17 | 2022-02-23     |          49 |
|  5 | 2018-10-21 | 2022-02-23     |          40 |
|  6 | 2018-05-25 | 2022-02-23     |          45 |
|  7 | 2018-03-12 | 2022-02-23     |          47 |
|  8 | NULL       | 2022-02-23     |        NULL |
+----+------------+----------------+-------------+

-- Apply DateDiff to the Table Data to know total years from the date of joining

select id,join_date,current_date(),
datediff(YEAR,join_date,current_date()) Total_years 
from demodb.public.emp1;

+----+------------+----------------+-------------+
| ID | JOIN_DATE  | CURRENT_DATE() | TOTAL_YEARS |
|----+------------+----------------+-------------|
|  1 | 2018-12-10 | 2022-02-23     |           4 |
|  2 | 2018-05-21 | 2022-02-23     |           4 |
|  3 | 2018-07-15 | 2022-02-23     |           4 |
|  4 | 2018-01-17 | 2022-02-23     |           4 |
|  5 | 2018-10-21 | 2022-02-23     |           4 |
|  6 | 2018-05-25 | 2022-02-23     |           4 |
|  7 | 2018-03-12 | 2022-02-23     |           4 |
|  8 | NULL       | 2022-02-23     |        NULL |
+----+------------+----------------+-------------+

How to subtract dates in Snowflake?

You can substract two dates in the Snowflake using the DATEDIFF function. For example:

select id,join_date,current_date(),
datediff(YEAR,join_date,current_date()) Total_years 
from demodb.public.emp1;

+----+------------+----------------+-------------+
| ID | JOIN_DATE  | CURRENT_DATE() | TOTAL_YEARS |
|----+------------+----------------+-------------|
|  1 | 2018-12-10 | 2022-02-23     |           4 |
|  2 | 2018-05-21 | 2022-02-23     |           4 |
|  3 | 2018-07-15 | 2022-02-23     |           4 |
|  4 | 2018-01-17 | 2022-02-23     |           4 |
|  5 | 2018-10-21 | 2022-02-23     |           4 |
|  6 | 2018-05-25 | 2022-02-23     |           4 |
|  7 | 2018-03-12 | 2022-02-23     |           4 |
|  8 | NULL       | 2022-02-23     |        NULL |
+----+------------+----------------+-------------+

Above example will find out the difference between the two date.

How to get number of years between two dates in Snowflake?

To get the number of years between the two dates we can use the datediff as follows:

select datediff(year, '2009-04-09','2013-10-08') as diff_years;

+------------+
| DIFF_YEARS |
|------------|
|          4 |
+------------+
1 Row(s) produced. Time Elapsed: 0.535s

How to get number of months between two dates in Snowflake?

To get the number of months between the two dates we can use the datediff as follows:

select datediff(month, '2009-04-09','2013-10-08') as diff_months;

+-------------+
| DIFF_MONTHS |
|-------------|
|          54 |
+-------------+
1 Row(s) produced. Time Elapsed: 0.397s

How to get number of days between two dates in Snowflake?

To get the number of days between the two dates we can use the datediff as follows:

select datediff(day, '2009-04-09','2013-10-08') as diff_days;

+-----------+
| DIFF_DAYS |
|-----------|
|      1643 |
+-----------+
1 Row(s) produced. Time Elapsed: 0.697s

How to get number of hours between two dates in Snowflake?

To get the number of hours between the two dates we can use the datediff as follows:

select datediff(hour, '2009-04-09','2013-10-08') as diff_hours;

+------------+
| DIFF_HOURS |
|------------|
|      39432 |
+------------+
1 Row(s) produced. Time Elapsed: 1.623s

How to get number of minute between two timestamp in Snowflake?

To get the number of minutes between the two timestamps we can use the datediff as follows:

select datediff(minute, '2013-05-08T23:39:20.123-07:00'::timestamp,
                                  '2013-05-09T23:29:20.123-07:00'::timestamp) as diff_minutes;

+--------------+
| DIFF_MINUTES |
|--------------|
|         1430 |
+--------------+
1 Row(s) produced. Time Elapsed: 0.338s

How to get number of seconds between two timestamp in Snowflake?

To get the number of seconds between the two timestamps we can use the datediff as follows:

select datediff(second, '2013-05-08T23:39:20.123-07:00'::timestamp,
                                  '2013-05-09T23:29:20.123-07:00'::timestamp) as diff_seconds;

+--------------+
| DIFF_SECONDS |
|--------------|
|        85800 |
+--------------+
1 Row(s) produced. Time Elapsed: 0.455s

When you should use DATEDIFF Function in Snowflake?

There are certain use case scenarios when it is recommended to use the DATEDIFF function within the Snowflake cloud data warehouse which are as follows:

  • If we want to find the difference between two dates in the form of days, years, or months. For example, We want to get the difference between Jan 10th 2021 and Jan 25th 2021, then the resultant output is 15 ( DATEDIFF(day,’2021-01-10′,’2021-01-15′) ).

Real World Use Case Scenarios for DATEDIFF Function in Snowflake

  • If you want to know the number of days of an employee from the date of joining.
  • If you want to calculate the age of user.

DATEDIFF Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about the DATEDIFF() function and its uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar