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 2005 Forums
 Transact-SQL (2005)
 Convert UTC Time to Local time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sonu
Posting Yak Master

110 Posts

Posted - 01/11/2010 :  15:58:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 01/11/2010 :  16:09:40  Show Profile  Visit webfred's Homepage  Reply with Quote
LA = German time - 9 hours


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

sonu
Posting Yak Master

110 Posts

Posted - 01/11/2010 :  16:12:09  Show Profile  Reply with Quote
quote:
Originally posted by webfred

LA = German time - 9 hours



I want the time in Franfurt, not LA.
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 01/11/2010 :  16:15:44  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 01/11/2010 :  16:33:18  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 01/11/2010 :  16:40:54  Show Profile  Reply with Quote
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)

Edited by - vijayisonly on 01/11/2010 16:41:43
Go to Top of Page

canada goose jackets
Starting Member

4 Posts

Posted - 12/14/2011 :  23:56:21  Show Profile  Reply with Quote
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
Go to Top of Page

canada goose jackets
Starting Member

4 Posts

Posted - 12/14/2011 :  23:56:27  Show Profile  Reply with Quote
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
Go to Top of Page

canada goose jackets
Starting Member

4 Posts

Posted - 12/14/2011 :  23:56:34  Show Profile  Reply with Quote
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
Go to Top of Page

canada goose jackets
Starting Member

4 Posts

Posted - 12/14/2011 :  23:56:39  Show Profile  Reply with Quote
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
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.08 seconds. Powered By: Snitz Forums 2000