Converting GMT to EST

[sds814]

I need to write a function that converts a time from GMT to EST taking daylight savings time into consideration.

On a high level I was thinking of doing the following:
a) a table that holds the daylight saving dates for this year and upcoming years. does anyone know a website that has these dates published?

b) if the date passed to the function is greater or equal to the date for the fall daylight saving date (i.e., 11/2013) but less than the spring daylight saving date (i.e., 3/2014) then subtract 6 from the gmt to get est

c) if the date passed to the function is greater or equal to the date for the fall daylight saving date (i.e., 3/2014) but less than the spring daylight saving date (i.e., 11/2014) then subtract 5 from the gmt to get est

 

[gmmastros]

There is a way to get the timeoffset right now. I'm not sure how you would do this for other dates and times.

There is a function named GetUTCDate and another called GetDate. The UTC version returns GMT time. To get the offset (in hours)....

Select DateDiff(Hour, GetUTCDate(), GetDate())

The GetDate function accommodates daylight savings time already because it will use the DateTime of the server where the database is running. That server will likely automatically adjust it's time to accommodate daylight savings time.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

Thanks for your help George. I just fount out a twist on the requirement.

The fall DST date is 11/3/2013 and lets say the current date is 11/4/2013. So if I do

Select DateDiff(Hour, GetUTCDate(), GetDate())

It should give me -5. Now the date that I need to convert from GMT to EST is 10/28/2013 5 PM, which is before the daylight saving time. If I do DATEADD(hh, DateDiff(Hour, GetUTCDate(), GetDate()), '2013-10-28 17:00:00.00') it would give 2013-10-28 12:00:00.000 instead of 2013-10-28 13:00:00.000. And this is because the result of datediff is after 10/28/2013.

Do you know how I could do that? DO I need to save the DST dates in a table and do a case statement of the date passed (10/28/2013) to the DST date?

 

[gmmastros]

Yes.

The trick I mentioned earlier really only works for "now". If you need to process dates that occurred in the past, or dates that occur in the future, then you will need to do something else.

If this were my project, I would create a table for daylight savings time. This table would have StartDate, EndDate and TimeOffset. You will then be able to query against this table to get the timeoffset.

The problem with this approach is that daylight savings time is not "set in stone". What I mean is... politicians can change when the switch to daylight savings time begins and/or ends. This is also further complicated when you start thinking about making this software work for other time zones. Additionally, there are some places on this wonderful earth of ours that have fractional hour differences in time zones.

Anyway... for a single time zone, something like this might work...

Declare @DaylightSavings Table(StartDate DateTime, EndDate DateTime, TimeOffset int)

Insert Into @DaylightSavings Values('20130310','20131103',-5)
Insert Into @DaylightSavings Values('20131104','20140304',-4)

Declare @SampleData Table(TheDate DateTime)

Insert Into @SampleData Values('20130320 10:45AM')
Insert Into @SampleData Values('20130420 10:45AM')
Insert Into @SampleData Values('20130520 10:45AM')
Insert Into @SampleData Values('20130620 10:45AM')
Insert Into @SampleData Values('20130720 10:45AM')
Insert Into @SampleData Values('20130820 10:45AM')
Insert Into @SampleData Values('20130920 10:45AM')
Insert Into @SampleData Values('20131020 10:45AM')
Insert Into @SampleData Values('20131120 10:45AM')
Insert Into @SampleData Values('20131220 10:45AM')
Insert Into @SampleData Values('20140120 10:45AM')
Insert Into @SampleData Values('20140220 10:45AM')
Insert Into @SampleData Values('20140320 10:45AM')
Insert Into @SampleData Values('20140420 10:45AM')

Select Data.TheDate As OriginalDate, 
       DateAdd(Hour, Daylight.TimeOffset, Data.TheDate) As ESTDate
From   @SampleData As Data
       Inner Join @DaylightSavings Daylight
         On Data.TheDate >= Daylight.StartDate
         And Data.TheDate < Daylight.EndDate

Of course, you'll want to create a real table instead of a table variable, and you will want to populate it with a lot of real data too. You'll need to make sure you go back as far as your data does, and you'll want to go as far in to the future as you can.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

Thanks for the help, George.