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?
Best Answer
Yes, it accounts for it:
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:
And when we try using a date with a different DST offset:
It assigns a different DST offset value, being aware of whether or not the date falls into DST.