Convert Local Time to UTC RRS feed

  • Question

  • Hi All,

    I work on the AEST timing (local time) with day light saving to be converted to UTC.

    How do we take day light savings into account?




    Wednesday, September 28, 2016 5:24 AM


All replies

  • Since SQL Server 2008 we have a type called datetimeoffset. It's really useful for this type of stuff.

    Then you can use the function SWITCHOFFSET to move it from one timezone to another, but still keeping the same UTC value.


    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 28, 2016 5:30 AM
  • You can do it like this:

    SELECT datediff(hour,GETUTCDATE(), getdate())

    For Eastern Standard Time that will be -5. So to convert your dates to UTC you need to do this:

    UPDATE Table SET Date = DATEADD(hour, -5, Date)


    Please click Mark As Answer if my post helped.

    Wednesday, September 28, 2016 5:41 AM

  • If the current values is stored in the table in UTC, how do we convert to AEST and also considering the day light savings.

    CREATE TABLE #table

    INSERT INTO #table VALUES ('20160929')

    SELECT * FROM #table


    • Edited by kccrga Thursday, September 29, 2016 1:54 AM
    Thursday, September 29, 2016 1:54 AM
  • Hi kccrga,

    Then you will need to CASE statement to deal with the case. Since SQL Server 2016, you could also take a look at AT TIME ZONE (Transact-SQL).

    Sam Zha
    TechNet Community Support

    Thursday, September 29, 2016 8:57 AM