Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
8781 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
1231 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
8781 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  
 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.06 seconds. Powered By: Snitz Forums 2000