Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005
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)
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)
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)
FOR READ-ONLY Use this(inspired by Bob Albright's incorrect solution ):
SELECT
date1,
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 (which, for edt, is 07:00 UTC at the start, and 06:00 GMT at the end).
CASE WHEN YEAR(date1) <= 2006 THEN
CASE WHEN
date1 >= '4/' + CAST((8-DATEPART(dw,'4/1/' + CAST(YEAR(date1) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(date1) as varchar) + ' 7:00'
AND
date1 < '10/' + CAST(32-DATEPART(dw,'10/31/' + CAST(YEAR(date1) as varchar)) as varchar) + '/' + CAST(YEAR(date1) as varchar) + ' 6: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. In 2008
-- daylight saving time ended at 02:00 edt (06:00 UTC) on Sunday, November 2, and in 2009 it began at 02:00 edt (07:00 UTC) on Sunday, March 8
CASE WHEN
date1 >= '3/' + CAST((8-DATEPART(dw,'3/1/' + CAST(YEAR(date1) as varchar)))%7 + 8 as varchar) + '/' + CAST(YEAR(date1) as varchar) + ' 7:00'
AND
date1 < '11/' + CAST((8-DATEPART(dw,'11/1/' + CAST(YEAR(date1) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(date1) as varchar) + ' 6:00'
THEN -4 ELSE -5 END
END
, date1) as date1Edt
from MyTbl
I posted this answer after I tried to edit Bob Albright's wrong answer. I corrected the times and removed superfluous abs(), but my edits were rejected multiple times. I tried explaining, but was dismissed as a noob. His is a GREAT approach to the problem! It got me started in the right direction. I hate to create this separate answer when his just needs a minor tweak, but I tried ¯\_(ツ)_/¯
A much simpler and generic solution that considers daylight savings. Given an UTC date in "YourDateHere":
--Use Minutes ("MI") here instead of hours because sometimes
-- the UTC offset may be half an hour (e.g. 9.5 hours).
SELECT DATEADD(MI,
DATEDIFF(MI, SYSUTCDATETIME(),SYSDATETIME()),
YourUtcDateHere)[LocalDateTime]