Convert from GMT to EDT - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Convert from GMT to EDT

Author

Topic

lw1990
Yak Posting Veteran

85 Posts

Posted - 2009-08-18 : 12:09:33

Hi,
I have a table which all the date/time value are saved as GMT. Now, I'm trying to display the records in a report as our local time(EDT), even in winter time it can be diplaied as EST. I'm sure there is one line of code to convert it, does anybone know it?
Ex. In my record, the time saved as: 2009-08-13 20:00:52.000 (GMT)
I need to display as: 2009-08-13 16:00:52.000 (EDT)

Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-08-18 : 12:31:06

[code]select
a.*,
EDT = dateadd(hour,-4,a.UDT),
EST = dateadd(hour,-5,a.UDT)
from
( --Test Data
select UDT = convert(datetime,'2009-08-13 20:00:52.000')
) a[/code]

Results:
[code]UDT EDT EST
----------------------- ----------------------- -----------------------
2009-08-13 20:00:52.000 2009-08-13 16:00:52.000 2009-08-13 15:00:52.000

(1 row(s) affected)[/code]

CODO ERGO SUM

lw1990
Yak Posting Veteran

85 Posts

Posted - 2009-08-18 : 13:16:42

Thank you Michael,
Yes, we can use dateadd(hour,-4,a.UDT) to convert to EDT, but after we switch to standard we have to change the code to dateadd(hour,-5,a.UDT)?
Does Microsoft create any standard reference to tell if it is EDT or EST and change the local time automatically?
Ex. 2009-08-13 20:00:52.000 (GMT) to 2009-08-13 16:00:52.000 (EDT)

and 2009-12-13 20:00:52.000 (GMT) to 2009-12-13 15:00:52.000 (EST)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-18 : 13:22:01

SELECT dateadd(hour, datediff(hour, getutcdate(), getdate()), UDT)

lw1990
Yak Posting Veteran

85 Posts

Posted - 2009-08-18 : 15:16:16

Thank you.
I forgot that getutcdate().

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-08-18 : 16:54:00

Be aware the this code only gives you the UTC offset at the time it is run:

datediff(hour, getutcdate(), getdate())

This means that it cannot tell you whet the offset was for a prior or future date. If you need that, you will need to store the start and end dates for daylight savings time in a table and do a lookup to see what the offset was for any UDT time.

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-08-18 : 23:28:14

china got it right. one timezone for the whole country. :)

elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-18 : 23:33:23

same here. Only one timezone for the whole country

EDIT :
just in case, you are not aware of the size of the country where i am, this will give you a good idea.

Driving from East to West takes only 30 mins. And this is not on a F1 Ferrari car.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-19 : 00:23:11

drive to singapore...5 hour =.=

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 00:24:44

5 hour ? where are you ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior