Converting dates to GetUTCDate

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

General SQL Server Forums

New to SQL Server Programming

Converting dates to GetUTCDate

Author

Topic

ds12will
Starting Member

21 Posts

Posted - 2009-10-29 : 11:31:11

Hello,

I have datetime data that was created using GetDate() function, how would I go about converting that data to UTC time using GetUTCDate() function?

For example if I were to select the data

Select DATECREATED from Table1
all date data pulls back

how could I use a similar simple query to see data converted to UTC time?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-29 : 12:06:54

Search this site for "convert utc". There is no SQL function to convert fron one to the other since it depends on where you, Daylight savings time, etc.

Jim

Everyday I learn something that somebody else already knew

ds12will
Starting Member

21 Posts

Posted - 2009-10-29 : 12:14:25

quote:
Originally posted by jimf

Search this site for "convert utc". There is no SQL function to convert fron one to the other since it depends on where you, Daylight savings time, etc.

Jim

Everyday I learn something that somebody else already knew

So I can't do a convert(getUTCdate) or anything like that? I think what you're saying is making sense.

ds12will
Starting Member

21 Posts

Posted - 2009-10-29 : 12:16:30

because I found this, where if I can take what's in their and maniupulate it somehow.

DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()

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

-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @UTCDate)

SELECT @LocalDate, @UTCDate, @LocalDate2

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-29 : 13:12:04

How, if at all, will Daylight Savings time affect your calculations? I'm not sure where you are, but here in the US we set our clocks back one hour on Sunday at 2:00 AM, effectively giving us 25 hours in one day, and next Spring we set them forward one hour (and 2:00 AM just disappears)

Jim

Everyday I learn something that somebody else already knew

ds12will
Starting Member

21 Posts

Posted - 2009-10-29 : 14:20:38

Hey Jim,

Being in Ohio, yes I would be impacted by DST.

Also on a side question, running a function is it possible to pass through an entire column of a table to get the conversion of all not just 1.

For example the following error pops up with the querey I found (There was a premade function I found on this forum)

If I run
SELECT dbo.Timezone_Conversion('CST', 'GMT', (Select DATECREATED from dbo.TimeZoneTest), 0)

I get
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28712
^^^Where I got the function

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-29 : 14:25:46

Yes, just a tweak to your syntax should do the trick

SELECT [ConvertedTime] = dbo.Timezone_Conversion('CST', 'GMT',DATECREATED), 0)

FROM from dbo.TimeZoneTest

Jim

Everyday I learn something that somebody else already knew

ds12will
Starting Member

21 Posts

Posted - 2009-10-29 : 15:17:35

Why do you ask about DST?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources