2

I have to convert UTC dates for some reports. We are using SQL Server 2016 and all my report queries are SPs, so I was able to do this:

SELECT MyDateTime AT TIME ZONE 'Pacific Standard Time' AS 'MyLocalDateTime'

This returns the expected value, but I have not been able to determine if this will still work during daylight savings time.

By querying the time zone info DMV:

 select * from sys.time_zone_info

I see there is a column for 'Is DST', so I am assuming the server is tracking that. So can I safely assume sql server will adjust the time as needed during DST?

2
  • Have you tried the same value with 'Pacific Daylight Time'? Jan 18, 2017 at 22:07
  • No, but that does not exist in my time_zone_info list. Is it a legit zone?
    – BattlFrog
    Jan 18, 2017 at 22:11

2 Answers 2

3

Yes, it accounts for it:

AT TIME ZONE applies specific rules for converting input values in smalldatetime, datetime and datetime2 data types, that fall into an interval that is affected by the DST change

As per the MSDN article on the expression. The sys.time_zone_info column supplies information on if the zone currently is in DST, but the article above indicates that the rules take into account DST given a specific date time value.

Example:

/*------------------------
SELECT SYSUTCDATETIME() AT TIME ZONE 'Eastern Standard Time';
------------------------*/

----------------------------------
2017-01-18 22:18:12.3789253 -05:00

And when we try using a date with a different DST offset:

/*------------------------
SELECT CAST('2017-07-18' AS DATETIME) AT TIME ZONE 'Eastern Standard Time';
------------------------*/

----------------------------------
2017-07-18 00:00:00.000 -04:00

It assigns a different DST offset value, being aware of whether or not the date falls into DST.

0

In general avoid using specific standard or daylight time zones. So instead of Eastern Daylight Time or Eastern Standard Time use the geographical style reference like America/New_York. The later will give you the proper Daylight or Standard time conversion based on the date being converted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.