SQL Server 2017 AT TIME ZONE & sys.time_zone_info

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

Answer 1

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  

Answer 2

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