Tuesday, January 17, 2012

Applying DATEDIFF to DateTimeOffset in SQL Server

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.

3 comments:

Knox Harrington said...

Thanks a lot for this post, this saved my life today!

Matthieu

Anonymous said...

For anyone who comes and finds this, like I just did...

The behavior is odd, but run this query:

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( MINUTE, @timeInZone1, @timeInZone2 );

The result is 1439, which is what you would expect. Thus "1" result seems to be due to rounding at the datepart. However, the same thing does not happen if you use DATETIME without timezone so the behavior is different and odd. In that case it does return zero. Still, going deeper precision than wanted is the correct approach and seems to resolve it. See:

https://www.simple-talk.com/sql/t-sql-programming/how-to-get-sql-server-dates-and-times-horribly-wrong/

Anonymous said...

After googling around, for two days, this post made my life....thanks