Unexpected results from time zone conversions

[R,S]

I was trying out the time zone conversions in SQL Server 2017 and ran across some unexpected results. The same anomalies show up in PowerShell, since they are both using the underlying time zone information in Windows.


Here is a good example in PowerShell:

$d = Get-Date '2019-10-26 20:30:00 -04:00' # Eastern Time doesn't fall back to -05:00 until November​

@( [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($d, 'W. Europe Standard Time'), [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($d, 'Central Europe Standard Time'), [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($d, 'E. Europe Standard Time') )​

(There are two lines of code there - the first one starting with "$d" and the second one starting with "@(".)​

The same example in SQL Server 2017:

declare @nyTime datetimeoffset = convert(datetimeoffset, N'2019-10-26 20:30:00.000000 -04:00', 121);​

select @nyTime at time zone N'W. Europe Standard Time' [W. Europe Time],
@nyTime at time zone N'Central Europe Standard Time' [Central Europe Time],
@nyTime at time zone N'E. Europe Standard Time' [E. Europe Time];​

The result in all three time zones is 2:30 am local time. My understanding is that all of the EU changes to or from Summer time at the same time (through the end of March 2021, at least [end of time changes in EU]), so shouldn't the local time in the adjacent time zones always have offsets that are one hour apart, even on the day that the offsets change?

The server is running Windows Server 2016 with the February 2019 update KB4487006.

Continue reading...