What does DATEDIFF do?
Today we’ll look at one of the most well-known and really frequently used functions in SQL Server: DATEDIFF. The DATEDIFF function belongs to the functions for time objects, with it you can calculate the time between two datetime timestamps. You are free to specify the interval in years, months, days, hours, minutes, seconds, milliseconds or nanoseconds, only the return value must be in the value range of INT.
How can you work with DATEDIFF?
As already mentioned, the DATEDIFF function calculates the distance between two DateTime values. Let’s look at an example from the WideWorldImporters database in the Sales.Orders table. If we want to know here how many days were between the first and the last order of a customer, i.e. how long the customer has been our customer, we can do this as follows
SELECT
MIN(OrderDate) AS MinOrderDate
,MAX(OrderDate) AS MaxOrderDate
,DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) as DaysBetweenOrders
,CustomerID
FROM Sales.Orders
GROUP BY CustomerID
ORDER BY DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) DESC
Of course, we can also have this time interval displayed in months or years:
SELECT
MIN(OrderDate) AS MinOrderDate
,MAX(OrderDate) AS MaxOrderDate
,DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) as DaysBetweenOrders
,DATEDIFF(MONTH, MIN(OrderDate), MAX(OrderDate)) as MonthsBetweenOrders
,DATEDIFF(YEAR, MIN(OrderDate), MAX(OrderDate)) as YearsBetweenOrders
,CustomerID
FROM Sales.Orders
GROUP BY CustomerID
ORDER BY DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) DESC
But we can also look at how long ago a customer’s last order was placed, for example:
SELECT
MIN(OrderDate) AS MinOrderDate
,MAX(OrderDate) AS MaxOrderDate
,DATEDIFF(DAY, MAX(OrderDate), GETDATE()) as DaysSinceLastOrder
,CustomerID
FROM Sales.Orders
GROUP BY CustomerID
ORDER BY DATEDIFF(DAY, MAX(OrderDate), GETDATE()) DESC
If you now want to calculate how much time was on average between the orders of a customer, you can do this with a CTE:
;WITH cte_dates AS (
SELECT
DATEDIFF(DAY,
OrderDate,
LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
) DaysBetweenOrders
,CustomerID
FROM Sales.Orders
)
SELECT
AVG(CAST(DaysBetweenOrders AS decimal(10,2)))
,CustomerID
FROM cte_dates
GROUP BY CustomerID
But as written above, this only works as long as the return value is within the value range of INT. You can determine the maximum value for an integer in SQL as follows:
SELECT -1-POWER(-2,31);
This gives us the value 2147483647, so if we look at the nanosecond portion of time, two dates between which we want to determine the nanoseconds can be a maximum of 2.15 seconds apart. Let’s try this, the following query returns an overflow error:
DECLARE @currentDate datetime = GETDATE() SELECT DATEDIFF(NANOSECOND, DATEADD(SECOND, -3, @currentDate), @currentDate)
However, if we reduce the time window to 2 seconds, we get a result:
DECLARE @currentDate datetime = GETDATE() SELECT DATEDIFF(NANOSECOND, DATEADD(SECOND, -2, @currentDate), @currentDate)
Let us now consider the maximum distance between two timestamps when they are separated by the maximum INT value in milliseconds or seconds:
DECLARE @MaxInt int = -1-POWER(-2,31);
DECLARE @currentDate datetime = GETDATE()
SELECT
DATEDIFF(HOUR, @currentDate, DATEADD(MILLISECOND, @MaxInt, @currentDate)) MaxMSHours
,DATEDIFF(DAY, @currentDate, DATEADD(MILLISECOND, @MaxInt, @currentDate)) MaxMSDays
,DATEDIFF(YEAR, @currentDate, DATEADD(MILLISECOND, @MaxInt, @currentDate)) MaxMSYears
,DATEDIFF(HOUR, @currentDate, DATEADD(SECOND, @MaxInt, @currentDate)) MaxSHours
,DATEDIFF(DAY, @currentDate, DATEADD(SECOND, @MaxInt, @currentDate)) MaxSDays
,DATEDIFF(YEAR, @currentDate, DATEADD(SECOND, @MaxInt, @currentDate)) MaxSYears
You can see that two timestamps that you want to know the distance in milliseconds can be a maximum of 25 days apart, while two timestamps that you want to know the distance in seconds can be a maximum of 68 years apart before the overflow of INT occurs. That’s pretty granular for such time spans.
