Why SQL timezone conversion for Atlantic standard time is wrong?

Fathimathul Farzidha 0
Viewed 39

Atlantic standard time is (-04:00), but when I run the below query am getting (-03:00)

SELECT 
    DATEADD(MINUTE, DATEPART(tz, GETUTCDATE() AT Time Zone 'Atlantic Standard Time'), GETUTCDATE()) AS atantic_ts

For example:

  • getutcdate() : 2022-09-12 16:00:00.000
  • expected : 2022-09-12 12:00:00.000
  • Actual output: 2022-09-12 13:00:00.000

It's not giving the proper current date time of Atlantic Standard Time.

Same issue occur for some other timezones like 'Alaskan standard time' also.

timezone sql-server
1 Answers

FYI - you don't need to add the minutes to get to the correct time. You can 'stack' AT TIME ZONE:

Declare @currentDateTime datetimeoffset = '2022-09-12 16:00:00.0000000 +00:00';  --UTC

 Select CurrentDateTime = @currentDateTime
      , ASTDateTime     = @currentDateTime At Time Zone 'Atlantic Standard Time'
      , GetUTCDateTime  = getutcdate() At Time Zone 'UTC' At Time Zone 'Atlantic Standard Time'
      , GetUTCDateTime  = cast('2022-09-12 16:00:00.000' As datetime) At Time Zone 'UTC' At Time Zone 'Atlantic Standard Time';
usersWA210
Related