Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 GMT to EST Time Zone

Author  Topic 

Kathy24
Starting Member

1 Post

Posted - 2013-03-12 : 11:08:21
I have a table that has DateSent column in GMT. I wish to convert this to EST. How do I do this in SQL Server? Need to consider the Day light saving as well.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-12 : 12:35:55
If you are doing this calculation live - i.e., on the current data, you can convert from GMT to UTC like shown below:
DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate() ), YourDateColumn)
If you need to convert historical dates, i.e, the "yourDateColumn" values are not today's dates, then you will need to create a reference table which has start and end dates of daylight savings time for each year, and then join to that table and subtract/add one hour based on the current date.

If you google for it, you might see some examples where they try to calculate the start and end of daylight savings time. I would stay away from that. The start and end of daylight saving time was changed in 2007, and Congress may change it again when they are not otherwise engaged, so best to keep the data in reference table and use that.

I had the reference table with all the US and European daylight savings date ranges the past 20 years or so and the code to convert GMT correctly to local time, all at my old work place. Unfortunately, Friday was my last day there and I didn't take any code with me when I left.
Go to Top of Page
   

- Advertisement -