locked
SQL Server 2017 AT TIME ZONE & sys.time_zone_info RRS feed

  • Question

  • User1203305613 posted

    a)The question is pertaining to "AT TIME ZONE "


    SELECT getdate() AT TIME ZONE 'Eastern Standard Time' This returns 2019-02-23 12:31:01.937 -05:00 , how do apply the offset so the result would be as 2019-02-23 7:31:01.937

    b)The Second question is about the view sys.time_zone_info
    select * from sys.time_zone_info where name in
    ('Eastern Standard Time','Central Standard Time','Pacific Standard Time','Mountain Standard Time')

    The query returns 

    name current_utc_offset is_currently_dst
    Pacific Standard Time -08:00 0
    Mountain Standard Time -07:00 0
    Central Standard Time -06:00 0
    Eastern Standard Time -05:00 0

    How does it look after Daylight Savings say in Winter (Dec 25 ) would Eastern Standard Time  returned from sys.time_zone_info look like  

    Eastern Standard Time -04:00 1

    Saturday, February 23, 2019 7:05 AM

Answers

  • User-893317190 posted

    Hi KALYANA ALLAM,

    Not clear about how sqlsver decides  is_currently_dst, but if you want to apply  current_utc_offset, as wmec  has suggested , you could use dateadd, so you need to know the utc time, you could get the utctime through GETUTCDATE().

    Below is my code.

    select 
    case when is_currently_dst =1   -- when applies daylight saving time
    
    then
    dateadd(hour,cast(left(current_utc_offset,3) as int)+1,GETUTCDATE())   --cast(left(current_utc_offset,3))  get the first three character and convert it to int 
    else   -- when  daylight saving time is not applied
    dateadd(hour,cast(left(current_utc_offset,3) as int),GETUTCDATE())
    end
     ,[name] from sys.time_zone_info 
    

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 25, 2019 6:40 AM

All replies

  • User364663285 posted

    Hi Kalayana,

    If you want to directly show the time correctly, how about to add hours to relevant column like

    select dateadd(HOUR, 7, getdate()) as time_added, 
           getdate() as curr_date  
    

    Sunday, February 24, 2019 3:32 PM
  • User-893317190 posted

    Hi KALYANA ALLAM,

    Not clear about how sqlsver decides  is_currently_dst, but if you want to apply  current_utc_offset, as wmec  has suggested , you could use dateadd, so you need to know the utc time, you could get the utctime through GETUTCDATE().

    Below is my code.

    select 
    case when is_currently_dst =1   -- when applies daylight saving time
    
    then
    dateadd(hour,cast(left(current_utc_offset,3) as int)+1,GETUTCDATE())   --cast(left(current_utc_offset,3))  get the first three character and convert it to int 
    else   -- when  daylight saving time is not applied
    dateadd(hour,cast(left(current_utc_offset,3) as int),GETUTCDATE())
    end
     ,[name] from sys.time_zone_info 
    

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 25, 2019 6:40 AM