Need to Convert GMT timestamp to EST timestamp

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

SQL Server Development (2000)

Need to Convert GMT timestamp to EST timestamp

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-10 : 08:43:33

Soloman writes "Hello,

I currently have a timestamp in a table (sql2000) that records in Greenwich Mean Time.

When running my crystal report I need the time converted from GMT to EST (with day light savings).

Any suggestions would be appreciated!

Thanks,
Soloman"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-10 : 08:47:18

This is a little convoluted, but it works:

SELECT DateAdd(hour, DateDiff(hour, getutcdate(), getdate()), myDateCol) FROM myTable

GETUTCDATE() should automatically adjust for daylight savings (haven't tested it)

sness
Starting Member

7 Posts

Posted - 2006-08-03 : 13:18:16

This works perfect, thanks.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-03 : 13:24:58

you should REALLY, REALLY, REALLY do this in crystal reports. really.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-03 : 14:31:38

quote:
Originally posted by robvolk

This is a little convoluted, but it works:

SELECT DateAdd(hour, DateDiff(hour, getutcdate(), getdate()), myDateCol) FROM myTable

GETUTCDATE() should automatically adjust for daylight savings (haven't tested it)

You may have a couple of issues withy this approach:
1. It may not have been daylight savings time when the data was captured, so the current offset to UTC(GMT) may not have been the offset then.
2. The server may not be set to Eastern Time.

I have found it is better the have the info about offset to UTC stored in a table with a date range showing the offset to UTC for each time zone/time range. It's even more important to do it this way if you are dealing with multiple time zones, each with it's own rules and start dates.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources