Convert UTC Time to Local time

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 UTC Time to Local time

Author

Topic

sonu
Posting Yak Master

110 Posts

Posted - 2010-01-11 : 15:58:36

Hi, I am using the below sql to convert the local datetime to the local time in Frankfurt, which is UTC+1.

DECLARE @LocalDate DATETIME, @UTCDate DATETIME
SET @LocalDate = GetDate()
-- convert local date to utc date
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GetDate()), @LocalDate)
print GetDate()
print @UTCDate
print DATEADD(HOUR, 1, @UTCDate)

Output:

Jan 11 2010 12:32PM
Jan 11 2010 4:32AM
Jan 11 2010 5:32AM

Now does this mean, that if its 12:32PM in LA, then its 5:32AM in Franfurt? That seems be incorrect though. It should be 9:32PM in Franfurt. I am confused.

Our server is sql 2005. The server is located in LA.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-11 : 16:09:40

LA = German time - 9 hours

No, you're never too old to Yak'n'Roll if you're too young to die.

sonu
Posting Yak Master

110 Posts

Posted - 2010-01-11 : 16:12:09

quote:
Originally posted by webfred

LA = German time - 9 hours

I want the time in Franfurt, not LA.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-01-11 : 16:15:44

Declare @UTC datetime,@Frankfort datetime,@Central datetime, @Pacific datetime
select @UTC = GETUTCDATE()
,@Frankfort = dateadd(hour,1,GETUTCDATE())
,@Central = dateadd(hour,-6,GETUTCDATE())
,@Pacific = dateadd(hour,-8,GETUTCDATE())
print @UTC
print @Frankfort
print @Central
print @Pacific

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-11 : 16:33:18

quote:
Originally posted by sonu

quote:
Originally posted by webfred

LA = German time - 9 hours

I want the time in Franfurt, not LA.

Frankfurt = LA + 9 hours

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 16:40:54

quote:
Originally posted by sonu

Hi, I am using the below sql to convert the local datetime to the local time in Frankfurt, which is UTC+1.

DECLARE @LocalDate DATETIME, @UTCDate DATETIME
SET @LocalDate = GetDate()
-- convert local date to utc date
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GetDate()), @LocalDate)
print GetDate()
print @UTCDate
print DATEADD(HOUR, 1, @UTCDate)

Output:

Jan 11 2010 12:32PM
Jan 11 2010 4:32AM
Jan 11 2010 5:32AM

Now does this mean, that if its 12:32PM in LA, then its 5:32AM in Franfurt? That seems be incorrect though. It should be 9:32PM in Franfurt. I am confused.

Our server is sql 2005. The server is located in LA.

The problem with your query is..while calculating @UTCdate..it shud have been..

SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GetDate(),GETUTCDATE()),@LocalDate)

canada goose jackets
Starting Member

4 Posts

Posted - 2011-12-14 : 23:56:21

ity
http://www.canadagooselover.com/ canada goose
moe
[url=http://www.canadagooselover.com/]canada goose jackets[/url]
qpk
<a href=http://www.canadagooselover.com/]canada goose jackets</a>canada goose

Saginaw crossover

canada goose jackets
Starting Member

4 Posts

Posted - 2011-12-14 : 23:56:27

canada goose jackets
Starting Member

4 Posts

Posted - 2011-12-14 : 23:56:34

canada goose jackets
Starting Member

4 Posts

Posted - 2011-12-14 : 23:56:39

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources