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

Maintain a TimeZone table, or shell out with an extended stored proc (xp_cmdshell or a COM component, or your own) and ask the OS to do it. If you go the xp route, you'd probably want to cache the offset for a day.

Answer 2

I had an issue similar to this at my company a few months ago where we wanted to store times from many different time zones.

What I found was that SQL Server does not store time zone information for a DateTime value in any way. It would require extra bits to do this which SQL Server needs for storing the date.

What we had to do was handle it through code. Sending times into the DB, we converted everything to Universal time before it was ever sent to the DB. When pulling times out of the DB, we converted it to whatever time zone we wanted using code immediately after it was pulled from the DB.

I'd recommend standardizing all dates as universal time if you think you'll ever have to deal with time zones.

Answer 3

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)

Answer 4

If either of these issues affects you, you should never store local times in the database:

1. With DST is that there is an "hour of uncertainty" around the falling back period where a local time cannot be unambiguously converted. If exact dates & times are required, then store in UTC.
2. If you want to show users the date & time in their own timezone, rather than the timezone in which the action took place, store in UTC.

Answer 5

If you're in the US and only interested in going from UTC/GMT to a fixed time zone (such as EDT) this code should suffice. I whipped it up today and believe it's correct but use at your own risk.

Adds a computed column to a table 'myTable' assuming your dates are on the 'date' column. Hope someone else finds this useful.

ALTER TABLE myTable ADD date_edt AS 
  dateadd(hh, 
        -- The schedule through 2006 in the United States was that DST began on the first Sunday in April 
        -- (April 2, 2006), and changed back to standard time on the last Sunday in October (October 29, 2006). 
        -- The time is adjusted at 02:00 local time.
              CASE WHEN YEAR(date) <= 2006 THEN  
                    CASE WHEN 
                              date >=  '4/' + CAST(abs(8-DATEPART(dw,'4/1/' + CAST(YEAR(date) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' 
                          AND 
                              date < '10/' + CAST(32-DATEPART(dw,'10/31/' + CAST(YEAR(date) as varchar)) as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' 
                    THEN -4 ELSE -5 END
              ELSE
        -- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007. 
        -- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on 
        -- the first Sunday of November, one week later than in years past. This change resulted in a new DST period 
        -- that is four weeks (five in years when March has five Sundays) longer than in previous years.[35] In 2008 
        -- daylight saving time ended at 02:00 on Sunday, November 2, and in 2009 it began at 02:00 on Sunday, March 8.[36]
                    CASE WHEN 
                              date >= '3/' + CAST(abs(8-DATEPART(dw,'3/1/' + CAST(YEAR(date) as varchar)))%7 + 8 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' 
                          AND 
                              date < 
                                '11/' + CAST(abs(8-DATEPART(dw,'11/1/' + CAST(YEAR(date) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' 
                    THEN -4 ELSE -5 END
              END
  ,date)

Answer 6

In Answer 7, the following SQL

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

might more accurately be:

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

(above code not tested)

The reason for this is that the sql "between" statement is inclusive. On the back-end of DST, this would result in a 2AM time NOT being converted to 1AM. Of course the likelihood of the time being 2AM precisely is small, but it can happen, and it would result in an invalid conversion.