Does changing server time zone "shift" SQL Server stored date / time values in ODBC / JDBC?

Question

I've read that SQL Server:

• uses system time zone on Windows (reg key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\TimeZoneKeyName)
• uses TZ env var on Linux or /etc/timezone.

I wonder what happens with data stored in DB if I change system time zone on the server and restart it.

Should I see the same dates/time returned by columns of type datetime2 via JDBC / ODBC API?

Will there be a gap equals to TZ difference after the change?

Doesn't that ruin technique of storing data in UTC? Like you are obligated not only to "normalize" dates (to UTC) but also keep SQL Server instances running in UTC...

Answer 1

As mentioned in DATETIME2 documentation, there is no time zone information stored, so DATETIME2 (as well as DATETIME, DATE and TIME) data is not effected if server's time zone is changed. If you insert '2023-01-01 14:15' while the server's time zone is UTC, a SELECT on the same record will return '2023-01-01 14:15' even if server's time zone is changed to Eastern Standard Time (UTC-5).

If you need to keep track of time zone of each record, you should use DATETIMEOFFSET datatype. Again, even if you change server's time zone, DATETIMEOFFSET data will keep the time zone that was specified when inserted/updated.

However, changing server's time zone will affect result of GETDATE()/SYSDATETIME()/SYSDATETIMEOFFSET() functions: the first two will always return current time based on server's time zone, while SYSDATETIMEOFFSET() also includes the time zone information.

If you want to work with UTC time zone, it would be better to use GETUTCDATE() or SYSUTCDATE() to get current UTC time, regardless of server's time zone.