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.

Converting to DateTimeOffset in SQL Server 2008

SQL Server 2008 introduces a new temporal data type, DateTimeOffset. This is the only data type that can hold both a date-time value and time zone offset information, so it’s perfect for holding data collected from around the world.

I’m preparing an application to support global scalability so it’s time to upgrade some columns from DateTime to DateTimeOffset. Conversion however is not exactly trivial. Unlike for example PostgreSQL, SQL Server does not add timezone offset information. You’ll have to do that yourself.

Converting DateTime to DateTimeOffset
When altering the column type to DateTimeOffset type, the timezone offset is set to 0 which is UTC.
The actual offset at any moment in time is determined by the time zone offset (i.e. +1 hour for most of Western Europe) and daylight saving time (DST). So that’s 0 in winter and +1 hour in summer.

The basic time zone offset is fixed, but the date DST starts and ends is different every year (though totally predictable).
The data set I’m updating spans a time period from early 2009 up to now, January 2012. I wrote a simple function that determines the DST time offset based on the actual date that needs to be converted.

CREATE FUNCTION fn_DSTOffset( @date1 DATETIMEOFFSET )
RETURNS INT
AS
BEGIN
RETURN
CASE WHEN ( ( @date1 > '2009-3-29 2:00' AND @date1 <= '2009-10-25 2:00' )
    OR ( @date1 > '2010-3-28 2:00' AND @date1 <= '2010-10-31 2:00' )
    OR ( @date1 > '2011-3-27 2:00' AND @date1 <= '2011-10-30 2:00' ) )
    THEN 60 -- offset in minutes
    ELSE 0
    END
END
GO

SQL Server handles time zone offsets in minutes. The actual offset from UTC is the DST offset plus the base offset. A conversion would look like this:

ALTER TABLE [Data] ALTER COLUMN [Created] DATETIMEOFFSET NOT NULL;
GO
DECLARE @baseoffset INT;
SET @baseoffset = 60; -- UTC +1 = 60 minutes
UPDATE [Data] SET
   [Created]=SWITCHOFFSET([Created], @baseoffset + dbo.fn_DSTOffset( [Created] ) );
GO

The SWITCHOFFSET method applies the specified time zone offset without altering the date and time.