none
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?

    Regards,

    Kccrga


    -kccrga http://dbatrend.blogspot.com.au/

    Wednesday, September 28, 2016 5:24 AM

Answers

All replies

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

    http://msdn.microsoft.com/en-us/library/bb630289.aspx

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

    http://msdn.microsoft.com/en-us/library/bb677244.aspx

    Also

    https://www.jitbit.com/maxblog/17-sql-server-how-to-convert-datetime-to-utc/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

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

    Refer

    https://www.jitbit.com/maxblog/17-sql-server-how-to-convert-datetime-to-utc/


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

    INSERT INTO #table VALUES ('20160929')

    SELECT * FROM #table

    -kccrga http://dbatrend.blogspot.com.au/


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

    https://sqlperformance.com/2016/07/sql-plan/at-time-zone


    Sam Zha
    TechNet Community Support

    Thursday, September 29, 2016 8:57 AM
    Moderator