Developer Notes

Converting to DateTimeOffset in SQL Server 2008

Posted 13 Jan 2012

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.