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 0This 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.