# DATEDIFF SQL Server Function

By:   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Dates

##### Problem

Often, we need to calculate the difference between two dates and return the results in a desired date part or increment such as days, hours, minutes. Fortunately, SQL Server provides a function for this.

In this article I will demo the SQL functions DATEDIFF and DATEDIFF_BIG and share several examples of how to use each. I will also show you the limitations and how to work around them. You will also learn how to calculate how old the city of St. Augustine is in nanoseconds.

##### Solution

We will explore the DATEDIFF and DATEDIFF_BIG functions, show how they are used, and provide several examples.

## What is SQL Server DATEDIFF Function

DATEDIFF() is a basic SQL Server function that can be used to do date math.  Specifically, it gets the difference between 2 dates with the results returned in date units specified as years, months days, minutes, seconds as an int (integer) value.

Syntax:

`DATEDIFF( DatePart, StartDate, EndDate )`

## What is SQL Server DATEDIFF_BIG Function

DATEDIFF_BIG() is a SQL function that was introduced in SQL Server 2016. It can be used to do date math as well.  Specifically, it gets the difference between 2 dates with the results returned in date units specified as years, months days, minutes, seconds as a bigint value.

Syntax:

`DATEDIFF_BIG( DatePart, StartDate, EndDate )`

## How to use DATEDIFF and DATEDIFF_BIG

The usage for DATEDIFF and DATEDIFF_BIG are the same: DATEDIFF(datepart of return values, Start Date, End Date).

Examples:

```SELECT DATEDIFF( MILLISECOND, '07-04-2020', '07-05-2020') --> = 86400000

SELECT DATEDIFF_BIG( NANOSECOND, '07-04-2020', '07-05-2020') --> = 86400000000000
```

First Parameter: is a valid datepart argument which is one of the following from nanosecond to year:

DatePart Name Abbreviation
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

Second Parameter: is the Start Date.  A valid date, datetime, datetimeoffset, datetime2, smalldatetime, or time data type variable or a string that resolved to a datetime datatype.

Third Parameter: is the End Date. A valid date, datetime, datetimeoffset, datetime2, smalldatetime, or time data type variable or a string that resolved to a datetime datatype.

*This information can be found on Microsoft Docs!

## SQL Server DATEDIFF Examples

Below are basic examples using the most common datapart arguments.

Example SQL Server DATEDIFF Code Output
How many minutes in a day? SELECT DATEDIFF(MINUTE, '07-04-2020', '07-05-2020') 1440 minutes
How many hours in a day? SELECT DATEDIFF(HOUR, '01-01-2020', '01-02-2020') 24 Hours
How many days in a year? SELECT DATEDIFF(DAY, '01-01-2020', '12-31-2020') 365 Days
How many months in a year? SELECT DATEDIFF(MONTH, '01-01-2019', '01-01-2020') 12 Months
How many years from 2000 to 2020? SELECT DATEDIFF(YEAR, '01-01-2000', '01-01-2020') 20 Years

## When to use SQL Server DATEDIFF_BIG function

Use the DATEDIFF_BIG function when your results exceed the range of an integer value which is between (-2,147,483,648 to +2,147,483,647).

BIGINT has a range of (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807). It seems that it would be hard to exceed this range! But we will present an example that does exceed this range when using DATEDIFF_BIG.

## SQL Server DATEDIFF_BIG instead of DATEDIFF

How to exceed the DATEDIFF int return value? Microsoft Docs provides 2 examples of how to overflow the DATEDIFF integer return value which I demonstrate below and show how DATEDIFF_BIG works to get around the limitation.

```--1. For millisecond, max difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds.
--   the following will exceed this range.

SELECT DATEDIFF(MILLISECOND, '01-01-2020', '02-01-2020')
```

This results in the following error.

Msg 535, Level 16, State 0, Line 60
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Here is another example.

```--2. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds.
--   the following will exceed this range.

SELECT DATEDIFF(SECOND, '01-01-1950', '02-01-2020')
```

This results in the following error.

Msg 535, Level 16, State 0, Line 66
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Using DATEDIFF_BIG instead to avoid the above errors.

```--3. How to get around the integer limit using DATEDIFF_BIG function

SELECT DATEDIFF_BIG(MILLISECOND, '01-01-2020', '02-01-2020') --> = 2678400000
SELECT DATEDIFF_BIG(SECOND, '01-01-1950', '02-01-2020') –-> = 2211667200
```

## When DATEDIFF_BIG is not Big Enough

According to Microsoft Docs, DATEDIFF_BIG can only overflow if using nanosecond precision where the difference between enddate and startdate is more than 292 years, 3 months, 10 days, 23 hours, 47 minutes and 16.8547758 seconds.

This could be an issue if you want to know how many nanoseconds it has been since Americas oldest city, St. Augustine Florida was founded!

```--1. How many nanoseconds since St. Augustine was founded on Sept. 8, 1565?
SELECT DATEDIFF_BIG(NANOSECOND, '09-08-1565', GETDATE())
```

Bummer!! We get an error.

Msg 535, Level 16, State 0, Line 76
The datediff_big function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff_big with a less precise datepart.

Let's try this in multiple steps:

```--1. First get the number of Years since St. Augustine was founded
SELECT DATEDIFF_BIG(YEAR, '09-08-1565', GETDATE()) --> = 455

--2. Next, get the number of nanoseconds in a year
SELECT DATEDIFF_BIG(NANOSECOND, '01-01-2020 00:00:00.0000000', '12-31-2020 23:59:59.9999999') --> = 31622399999999900

--3. Last multiply the results together and cast to decimal(38,0)
-- 455 x 31622399999999900 = 14388191999999954500
SELECT CAST(455 * 31622399999999900 as DECIMAL(38,0)) --> = 14388191999999954500
```

Walla! Now we know how many nanoseconds since the founding of St. Augustine.

## Wrap Up

I hope you enjoyed this exercise on DATEDIFF and DATEDIFF_BIG. DATEDIFF is a commonly used SQL function that has been around for many years. DATEDIFF_BIG is a relatively new function that was introduced in SQL Server 2016. Though I provided examples of overflow from these functions it is rare that you would encounter these scenarios. These are handy functions that should be added to your SQL toolbox!

##### Next Steps

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.