Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert Local Time to UTC Expand / Collapse
Author
Message
Posted Thursday, January 27, 2011 9:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:28 PM
Points: 309, Visits: 730
Is there a good way to convert a local time to UTC time? I expected to find some sort of built in conversion functions, but I don't see one.

TestDate DateTime
insert into SomeDateTable(ThisDate) values(TestDate)

Since TestDate will be "local", what's the best way to convert that to UTC? I hate to brute force add the -7 from my timezone, that doesn't seem right.


.
Post #1054648
Posted Thursday, January 27, 2011 9:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 8:26 AM
Points: 2,386, Visits: 7,623
SELECT GETUTCDATE()




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1054683
Posted Thursday, January 27, 2011 1:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:28 PM
Points: 309, Visits: 730
A wonderful solution indeed if what I needed to do was get the date/time. Unfortunately, I'm trying to figure out how to store the date/time.

.
Post #1054910
Posted Thursday, January 27, 2011 1:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 1:46 PM
Points: 947, Visits: 2,747
This link mentions using the data type datetimeoffset and the function SWITCHOFFSET()

Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1054921
Posted Thursday, January 27, 2011 2:03 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:22 PM
Points: 1,446, Visits: 3,232
select datediff(hour,getdate(),getutcdate())

tells you how many hours the local time zone differs from UTC. Once you know that you can use dateadd() with any datetime column or vriable to get it to utc.




The probability of survival is inversely proportional to the angle of arrival.
Post #1054927
Posted Thursday, January 27, 2011 4:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:28 PM
Points: 309, Visits: 730
That sounds like it's worth a shot. I'm just surprised there's no existing function that handles this. I'm sure there's a reason, but I don't know what it is.

Thanks sturner.


.
Post #1055010
Posted Thursday, January 27, 2011 4:53 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:36 PM
Points: 3,118, Visits: 11,556
The real problem is that there is no universal answer, since the offset changes at least twice per year in places that observe daylight saving time, and the rules for it can change at any time at the whim of the local government.

This will give you an idea of the various rules in use around the world:
http://www.timeanddate.com/time/dst/2010a.html

Also, the time offset is ambiguous. On the day the time changes to set the time back one hour, you actually have the same local time twice with two different offsets to UTC time.

We store dates of the time offsets for each time zone of interest in a table and do lookups to do the conversion.

This works OK if you are only interested in the difference right now. If you are dealing with datetimes in the past or future, you will have to use a different method, like a lookup table.

select datediff(hour,getdate(),getutcdate())








Post #1055019
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse