none
Convert UTC Date time to local Date time in select statement RRS feed

  • Question

  • i got a code which is not clear to me how it convert UTC Date time to local Date time.

    please help me to understand below code. thanks

    SELECT CONVERT(datetime, 
                   SWITCHOFFSET(CONVERT(datetimeoffset, 
                                        MyTable.UtcColumn), 
                                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS ColumnInLocalTime
    FROM MyTable

    Tuesday, February 11, 2020 6:36 PM

Answers

  • create table MyTable (UtcColumn datetime)
    --select GETUTCDATE()
    insert into MyTable values('2020-02-11 18:56:20.840')
    
    SELECT 
     DATENAME(TzOffset, SYSDATETIMEOFFSET()) 
     ,CONVERT(datetimeoffset, UtcColumn)
     ,CONVERT(datetime, 
     SWITCHOFFSET(CONVERT(datetimeoffset, UtcColumn), 
     DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS ColumnInLocalTime
    FROM MyTable
      --  SWITCHOFFSET ( DATETIMEOFFSET, time_zone )   
    
    --https://docs.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?view=sql-server-ver15
    
    drop table MyTable


    Tuesday, February 11, 2020 6:59 PM
    Moderator
  • That is a very complicated way to convert a UTC datetime to local datetime.

    CONVERT(datetimeoffset, MyTable.UtcColumn) = MyTable.UtcColumn + " +00:00" to make a UTC datetimeoffset

    DATENAME(TzOffset, SYSDATETIMEOFFSET()) = current UTC offset based on server local time ie "-05:00"

    SWITCHOFFSET change datetimeoffset of " +00:00" to local offset ie "-05:00".

    A simpler way to accomplish the same thing is:

    DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()), MyTable.UtcColumn )

    • Marked as answer by Sudip_inn Friday, February 14, 2020 9:08 AM
    Tuesday, February 11, 2020 7:03 PM
    Moderator
  • Hi Sudip_inn, 

    Per your description , I add some additional information  for Jingyang Li's script . Please check .

    drop table MyTable
    go
    create table MyTable (UtcColumn datetime)
    --select GETUTCDATE()
    insert into MyTable values('2020-02-11 18:56:20.840')
    
    SELECT SYSDATETIMEOFFSET()
     /*2020-02-12 13:37:50.8219239 +08:00*/
     ,DATENAME(TzOffset, SYSDATETIMEOFFSET()) 
     /* +08:00 */
     ,CONVERT(datetimeoffset, UtcColumn)
     /*2020-02-11 18:56:20.8400000 +00:00*/
     ,CONVERT(datetime, 
     SWITCHOFFSET(CONVERT(datetimeoffset, UtcColumn), 
     DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS ColumnInLocalTime
     /*2020-02-12 02:56:20.840*/
    FROM MyTable
    

    ---SYSDATETIMEOFFSET()

    Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

    ---DATENAME

    This function returns a character string representing the specified datepart of the specified date.

    ---SWITCHOFFSET

    Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. For more information , please refer to SWITCHOFFSET (Transact-SQL)

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, February 14, 2020 9:07 AM
    Wednesday, February 12, 2020 5:53 AM

All replies

  • create table MyTable (UtcColumn datetime)
    --select GETUTCDATE()
    insert into MyTable values('2020-02-11 18:56:20.840')
    
    SELECT 
     DATENAME(TzOffset, SYSDATETIMEOFFSET()) 
     ,CONVERT(datetimeoffset, UtcColumn)
     ,CONVERT(datetime, 
     SWITCHOFFSET(CONVERT(datetimeoffset, UtcColumn), 
     DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS ColumnInLocalTime
    FROM MyTable
      --  SWITCHOFFSET ( DATETIMEOFFSET, time_zone )   
    
    --https://docs.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?view=sql-server-ver15
    
    drop table MyTable


    Tuesday, February 11, 2020 6:59 PM
    Moderator
  • That is a very complicated way to convert a UTC datetime to local datetime.

    CONVERT(datetimeoffset, MyTable.UtcColumn) = MyTable.UtcColumn + " +00:00" to make a UTC datetimeoffset

    DATENAME(TzOffset, SYSDATETIMEOFFSET()) = current UTC offset based on server local time ie "-05:00"

    SWITCHOFFSET change datetimeoffset of " +00:00" to local offset ie "-05:00".

    A simpler way to accomplish the same thing is:

    DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()), MyTable.UtcColumn )

    • Marked as answer by Sudip_inn Friday, February 14, 2020 9:08 AM
    Tuesday, February 11, 2020 7:03 PM
    Moderator
  • Hi Sudip_inn, 

    Per your description , I add some additional information  for Jingyang Li's script . Please check .

    drop table MyTable
    go
    create table MyTable (UtcColumn datetime)
    --select GETUTCDATE()
    insert into MyTable values('2020-02-11 18:56:20.840')
    
    SELECT SYSDATETIMEOFFSET()
     /*2020-02-12 13:37:50.8219239 +08:00*/
     ,DATENAME(TzOffset, SYSDATETIMEOFFSET()) 
     /* +08:00 */
     ,CONVERT(datetimeoffset, UtcColumn)
     /*2020-02-11 18:56:20.8400000 +00:00*/
     ,CONVERT(datetime, 
     SWITCHOFFSET(CONVERT(datetimeoffset, UtcColumn), 
     DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS ColumnInLocalTime
     /*2020-02-12 02:56:20.840*/
    FROM MyTable
    

    ---SYSDATETIMEOFFSET()

    Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

    ---DATENAME

    This function returns a character string representing the specified datepart of the specified date.

    ---SWITCHOFFSET

    Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. For more information , please refer to SWITCHOFFSET (Transact-SQL)

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, February 14, 2020 9:07 AM
    Wednesday, February 12, 2020 5:53 AM
  • i have few question

      1) what TzOffset does ?
      2) what SYSDATETIMEOFFSET() return?
      3) CONVERT(datetimeoffset, UtcColumn) what is datetimeoffset here?
      4) what SWITCHOFFSET function does ?

    Friday, February 14, 2020 3:07 PM
  • can you please post a sample code regarding your answer which i can execute in my pc. thanks
    Friday, February 14, 2020 3:08 PM
  • i have few question  1) what TzOffset does ?

    The offset from the UTC in a datetimeoffset value.

      2) what SYSDATETIMEOFFSET() return?

    The current time, including the current time zone.

      3) CONVERT(datetimeoffset, UtcColumn) what is datetimeoffset here?

    A data type in SQL Server that in addition to date and time also holds the time zone.

      4) what SWITCHOFFSET function does ?

    It changes the time zone for a datetime offset value. For instance, I ran this query:

    SELECT sysdatetimeoffset(), switchoffset(sysdatetimeoffset(), '-05:00')

    And I got back the current time in my time zone (Central European Time) and in US East Coast time:

    2020-02-15 00:05:47.3689474 +01:00     2020-02-14 18:05:47.3689474 -05:00


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 14, 2020 11:07 PM