# Explained: DATEDIFF() function in Snowflake?

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:

• 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.