# Snowflake DATEDIFF: 4 Quick Reference Examples

URL: https://select.dev/docs/resources/snowflake-developer-guide/snowflake-datediff
Index of all SELECT docs: https://select.dev/docs/llms.txt

> Learn how to leverage Snowflake's most popular date function: DATEDIFF

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.

![Snowflake DATEDIFF function syntax](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/snowflake-datediff-4-quick-reference-examples-0-0d7aa181a30473d8f597fc1f7f4656e063c6a05e07107e87c06674962b631c4e/width=1908,quality=75)

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

1. `**unit**`**:** the unit of time to calculate the difference in (eg. `'day'`, `'month'`, `'hour'`, etc.)
2. `**date/timestamp #1**`**:** the first date/timestamp
3. `**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).

```sql
 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')`.

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

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

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

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

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

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

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

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

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

```sql
select datediff(month, date'2024-04-01', date'2024-05-30') -- Output: 1
```