Convert Local Time to UTC

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

    .

  • 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/

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

    .

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

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

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

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

    .

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

  • 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;
  • 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