Difference of two date time in SQL Server

Defusing the "millisecond" bomb

If you need exact precision down to the millisecond, switch the DATEDIFF unit to millisecond:

-- Jack Bauer does not sleep, the clock ticks in milliseconds
SELECT DATEDIFF(millisecond, '2023-01-01T08:00', '2023-01-02T17:00') AS MillisecondsDiff;


For differences in seconds to ensure perfect precision, divide the result by 1000:

-- Just divide, no submenuethry
SELECT DATEDIFF(millisecond, '2023-01-01T08:00', '2023-01-02T17:00') / 1000.0 AS SecondsDiff;


Time-lapse from the present

To calculate the difference between a specific date-time and the current datetime, utilize the GETDATE() function:

-- How many times Earth rotated since the start date?
SELECT DATEDIFF(day, '2023-01-01T08:00', GETDATE()) AS DaysFromNow;


Face-off: DATEDIFF vs DATEADD

Suppose you need to adjust the date to the beginning of the hour before comparison. First, normalize the date using DATEADD, then apply DATEDIFF:

-- When you need to catch the train but time is relative
SELECT DATEDIFF(hour, DATEADD(hour, DATEDIFF(hour, 0, GETDATE()), 0), GETDATE()) AS HoursSinceHourStart;


Pimp my output

The CONVERT function helps to format date differences in an eye-pleasing, human-readable way:

-- Make it readable, not like my handwriting
SELECT CONVERT(varchar, DATEADD(second, DATEDIFF(second, '2023-01-01T08:00', '2023-01-02T17:00'), 0), 108) AS TimeHHMMSS;


Multi-zone wrestling

Do not forget about time zones when calculating across them. Use AT TIME ZONE to account for time zone differences:

-- Bi-zonal calc, Pacific VS Eastern time
SELECT DATEDIFF(hour, 
    '2023-01-01T08:00' AT TIME ZONE 'Eastern Standard Time', 
    '2023-01-02T17:00' AT TIME ZONE 'Pacific Standard Time') AS HoursDiffTimeZone;


Overloading the SELECT

You can stack the DATEDIFF within a single SELECT statement to execute multiple time calculations:

-- The more the merrier, right?
SELECT 
    DATEDIFF(day, StartTime, EndTime) AS DateDiffDays,
    DATEDIFF(hour, StartTime, EndTime) AS DateDiffHours,
    DATEDIFF(minute, StartTime, EndTime) AS DateDiffMinutes
FROM YourTable;


Gotcha! Boundary crossing

BOUNDARY CROSSING is important to consider when using DATEDIFF. This function counts the number of boundaries crossed, not the time elapsed.

-- New Year's resolution: Count the leaps, not the time!
SELECT DATEDIFF(year, '2023-12-31', '2024-01-01') AS YearBoundaryCrossed;


Leap seconds: The unseen enemies

SQL Server ignores leap seconds. Keep that in mind, or your calculations risking being "leap seconds" off.

Level-up: Advanced learning

Explore Microsoft Learn and MSDN documentation for a deep dive into the functionalities of datetime in SQL Server.