GMT to UTC world location conversion 1

[Wallie12]

Hello;
I have locations world wide, but my times are recorded in EST.
I need to convert to local time, within SQL 2008 using UTC.
Has any one done this?

Thanks

Brian MAhler

 

[gmmastros]

Take a look at the GETUTCDATE() function and the DATEADD function (which will allow you to add hours).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Wallie12]

Within the individual records I have different locations around the globe. I need to match the individual locations with the proper time zone and alter the datetimes accordingly.
I wanted to know if any one has built a table with UTC country, city, date, time to GMT date, time conversion.

Thanks.

 

[gmmastros]

Can you show some sample data and expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Wallie12]

INPUT
Country Starttime
--------------- --------------------
Australia Nov 24 2009 8:04PM
South Africa Mar 18 2010 5:02PM
India Mar 24 2010 9:18AM
United Kingdom Mar 22 2010 4:17AM
China Mar 24 2010 11:04PM

OUTPUT
Country ADJUSTED - Starttime
--------------- --------------------
Australia Nov 24 2009 8:04PM <==?
South Africa Mar 18 2010 5:02PM
India Mar 24 2010 9:18AM
United Kingdom Mar 22 2010 4:17AM
China Mar 24 2010 11:04PM

 

[Wallie12]

I should restate the question: conversion of EST time to UTC time by location.

 

[gmmastros]

I'm positive that some countries span multiple time zones. The united states spans quite a few, and I am sure China must span quite a few also.

You may want to take a look at geonames. There are lots of things to be downloaded from there that may be useful to you. Without looking too closely, I would suggest here:

http://download.geonames.org/export/dump/

Specifically a file named tz.zip. The zip file has a tz.txt that you should be able to import into SQL Server.

I'm not 100% certain that this is what you are looking for, but I do recommend you take a look. It's where I would start looking if this were my project.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Check the opposite problem, but may be of some help

http://social.msdn.microsoft.com/Fo...1dddfe0/#1c03c6d9-4715-4e08-96e8-a3ddf497ce0a

PluralSight Learning Library