January 27, 2011 at 9:02 am
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.
.
January 27, 2011 at 9:43 am
SELECT GETUTCDATE()
January 27, 2011 at 1:48 pm
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.
.
January 27, 2011 at 1:57 pm
This link mentions using the data type datetimeoffset and the function SWITCHOFFSET()
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 27, 2011 at 2:03 pm
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.
January 27, 2011 at 4:05 pm
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.
.
January 27, 2011 at 4:53 pm
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())
October 15, 2018 at 9:11 am
BSavoie - Thursday, January 27, 2011 9:02 AMIs 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 DateTimeinsert 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.
http://www.alekztgtipsdevs.com/2018/09/funcion-sql-para-convertir-fecha-utc.html
October 15, 2018 at 9:33 am
I suggest using MINUTE rather than HOUR as the difference between local and UTC. Yes, some places actually do offset by not-an-even-hour, so, to be safe, use minutes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply