SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 GMT to EST Time Zone
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kathy24
Starting Member

1 Posts

Posted - 03/12/2013 :  11:08:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3636 Posts

Posted - 03/12/2013 :  12:35:55  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000