none
Unexpected results while converting time zones using SQL Server 2017 on Windows Server RRS feed

  • Question

  • 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 "@(".)

    Image

    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];

    Image

    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.

    Monday, April 1, 2019 2:47 PM

All replies

  • Hi,

    For further help, I suggest you submit a new case on SQL server forum as they will be more professional on your issue:
    This is the SQL server forum link:
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. 

    Thank you for your understanding.

    Best regards,

    Yilia 


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, April 2, 2019 5:41 AM
    Moderator
  • I started in the SQL Server forum and they redirected me here, since "information (such as timezone rules) is maintained outside of SQL Server". That's understandable, and the reason I added the PowerShell example - the results are the same in PowerShell, which has nothing to do with SQL Server.
    • Edited by R,S Tuesday, April 2, 2019 2:13 PM
    Tuesday, April 2, 2019 2:12 PM
  • Hi,

    You could refer to those threads for more information:

    https://stackoverflow.com/questions/46834587/powershell-convert-local-time-to-different-timezone 

    https://www.craigforrester.com/posts/convert-times-between-time-zones-with-powershell/

    Note: This is a third-party link and we do not have any guarantees on this website. This is just for your convenience. And Microsoft does not make any guarantees about the content.

    Best regards,

    Yilia 


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, April 4, 2019 7:33 AM
    Moderator
  • Yes - as you can easily see in the PowerShell example in my original post, that "ConvertTimeBySystemTimeZoneId" function described in your second link is the one that example _is_ using. The question is... why are the time zone offsets for adjacent time zones all the same when "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?"

    Please read the original post.



    • Edited by R,S Thursday, April 4, 2019 6:00 PM
    Thursday, April 4, 2019 6:00 PM