# Snowflake DATEDIFF: 4 Quick Reference Examples

- Ian WhitestoneCo-founder & CEO of SELECT

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
```