Convert Local Time to UTC

Question

Convert Local Time to UTC

BSavoie

SSCertifiable

Points: 6309
More actions
January 27, 2011 at 9:02 am

#230823

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.
.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

SELECT GETUTCDATE()

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/

BSavoie SSCertifiable Points: 6309 More actions · Answer 2

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.

.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

This link mentions using the data type datetimeoffset and the function SWITCHOFFSET()

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

sturner SSC-Insane Points: 22411 More actions · Answer 4

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.

BSavoie SSCertifiable Points: 6309 More actions · Answer 5

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.

.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

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())

tregar666 Grasshopper Points: 15 More actions · Answer 7

BSavoie - Thursday, January 27, 2011 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 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

CREATE FUNCTION [fun_ConvertUTCToDateTimeZone](@datetimeUTC datetime, @TimeZoneToConvert nvarchar(60))

RETURNS datetime

AS

BEGIN

declare @fechaReturn datetime

set @fechaReturn = (SELECT CONVERT(datetime,@datetimeUTC)

AT TIME ZONE 'UTC'

AT TIME ZONE @TimeZoneToConvert);

Return @fechaReturn

END;

ScottPletcher SSC Guru Points: 101122 More actions · Answer 8

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".