Developer Notes

Applying DATEDIFF to DateTimeOffset in SQL Server

Posted 17 Jan 2012

In a recent post I described how you can upgrade columns from DateTime to DateTimeOffset and add the missing time zone offset. After you do that, you may notice that the DATEDIFF function does not work the way it did before.

For example:

DECLARE @timeInZone1 datetimeoffset, @timeInZone2 datetimeoffset

-- Two times on the same day in timezone UTC +1 (Western Europe)
SET @timeInZone1 = '2012-01-13 00:00:00 +1:00';
SET @timeInZone2 = '2012-01-13 23:59:59 +1:00';

SELECT DATEDIFF( day, @timeInZone1, @timeInZone2 );
-- result is 1 !!!

Not exactly what I had expected. DATEDIFF(day, x, y) will return the number of datelines crossed between x and y. Since both dates are on the same day in the same time zone you’d expect the function to return 0.

It returns 1 however because DATEDIFF does nothing with the time zone offset and compares the underlying UTC time in stead. In UTC the first date is actually January 12, 2012 23:00.

To get the result I had expected, I need to compare the local times not the UTC times. The trick is to convert to DateTime first:

SELECT DATEDIFF( day,
    CAST(@timeInZone1 AS DateTime), 
           CAST(@timeInZone2 AS DateTime) );
-- result is 0

This assumes however that both dates are in the same time zone. If that is not the case you can use SWITCHOFFSET to normalize on a time zone. Then cast the values to DateTime and apply the DATEDIFF function.

DECLARE @offset int;
SET @offset=120; -- UTC +2
SELECT DATEDIFF( day,
    CAST(SWITCHTIMEZONE(@timeInZone1, @offset) AS DateTime), 
           CAST(SWITCHTIMEZONE(@timeInZone1, @offset)@timeInZone2 AS DateTime) );
-- result is 0

UPDATE

Looks like I’m not the only one that was a little surprised about the behavior.