sql server - Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005

Question

What is the best way to convert a UTC datetime into local datetime. It isn't as simple as a getutcdate() and getdate() difference because the difference changes depending on what the date is.

CLR integration isn't an option for me either.

The solution that I had come up with for this problem a few months back was to have a daylight savings time table that stored the beginning and ending daylight savings days for the next 100 or so years, this solution seemed inelegant but conversions were quick (simple table lookup)

Answer 1

Create two tables and then join to them to convert stored GMT dates to local time:

TimeZones     e.g.
---------     ----
TimeZoneId    19
Name          Eastern (GMT -5)
Offset        -5

Create the daylight savings table and populate it with as much information as you can (local laws change all the time so there's no way to predict what the data will look like years in the future)

DaylightSavings
---------------
TimeZoneId    19
BeginDst      3/9/2008 2:00 AM
EndDst        11/2/2008 2:00 AM

Join them like this:

inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId
left join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone 
    and x.TheDateToConvert between ds.BeginDst and ds.EndDst

Convert dates like this:

dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, TheDateToConvert)