In short, introduced in MS SQL 2016, DATEDIFF_BIG is almost the same function as DATEDIFF starting from MS SQL 2008 that is commonly used already. So, let’s see how and for what is used and what are the benefits.

Syntax:

DATEDIFF_BIG (datepart, startdate, enddate) 

• Datepart – Time unit
  • 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)
• StartDate – And date/time value (time, date, datetime, etc.)
• Enddate – And date/time value (time, date, datetime, etc.)

The function returns a bigint value representing the count of the specified datepart units crossed between the specified startdate and enddate.

The main difference between the DATEDIFF and DATEDIFF_BIG is as you can notice in the “BIG” part of the name 😊 It’s almost the true, because while DATEDIFF return a value of INT type, the DATEDIFF_BIG returns BIGINT (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807).

Now let’s see some examples using both functions and the limitations.

Example:

DECLARE @StartDate DATETIME = GETDATE()
DECLARE @EndDate DATETIME = DATEADD(day, 1, @StartDate)

SELECT 
	DATEDIFF(WK , @StartDate, @EndDate) AS "Week diff"
	, DATEDIFF(DD , @StartDate, @EndDate) AS "Day diff"
	, DATEDIFF(HH , @StartDate, @EndDate) AS "Hour diff"
	, DATEDIFF(MI, @StartDate, @EndDate) AS "Minute diff"
	, DATEDIFF(SS, @StartDate, @EndDate) AS "Second diff"
	, DATEDIFF(MS, @StartDate, @EndDate ) AS "Millisecond diff"

In this example we can see that DATEDIFF is working well for an interval of 1 day and we were able to get the smallest unit of microsecond. Let’s try to see how will DATEDIFF handle the nanosecond.

Example:

DECLARE @StartDate DATETIME = GETDATE()
DECLARE @EndDate DATETIME = DATEADD(day, 1, @StartDate)

SELECT DATEDIFF(MCS, @StartDate, @EndDate ) AS "Microsecond diff"

Since the int data type has a range from -2,147,483,648 to +2,147,483,647 overflow occurred and we get the error message:

“Msg 535, Level 16, State 0, Line 21
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.”

Now, let’s try to get microseconds and even nanoseconds using the DATEDIFF_BIG function.

Example:

DECLARE @StartDate DATETIME = GETDATE()
DECLARE @EndDate DATETIME = DATEADD(day, 1, @StartDate)

SELECT 
	DATEDIFF_BIG(WK , @StartDate, @EndDate) AS "Week diff"
	, DATEDIFF_BIG(DD , @StartDate, @EndDate) AS "Day diff"
	, DATEDIFF_BIG(HH , @StartDate, @EndDate) AS "Hour diff"
	, DATEDIFF_BIG(MI, @StartDate, @EndDate) AS "Minute diff"
	, DATEDIFF_BIG(SS, @StartDate, @EndDate) AS "Second diff"
	, DATEDIFF_BIG(MS, @StartDate, @EndDate ) AS "Millisecond diff"
	, DATEDIFF_BIG(MCS, @StartDate, @EndDate ) AS "Microsecond diff"
	, DATEDIFF_BIG(NS, @StartDate, @EndDate ) AS "Nanosecond diff"

DATEDIFF_BIG function can handle much bigger values than the DATEDIFF function. Also, with DATEDIFF_BIG you can get the overflow error message, but I doubt that there are some justified cases. It can be used within the columns of the SELECT statement but also in the WHERE, HAVING, GROUP BY and ORDER BY clauses.

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.38 kb)