sys.time_zone_info lists timezone names and their current offsets. My SQL Server 2019 environment returned 141 rows.
SELECT name,current_utc_offset,is_currently_dst FROM sys.time_zone_info ORDER BY name;
SELECT COUNT(*) AS AvailableTimeZones FROM sys.time_zone_info;Counts depend on installed definitions and environment rather than only engine version. Query the current instance. This catalog has been available since SQL Server 2016.
current_utc_offset and is_currently_dst describe present state. They don’t give the applicable offset for every historical or future date. Use AT TIME ZONE with the intended named zone for date-specific conversion.
Operating-system updates can change zone definitions. Daylight-saving transitions produce ambiguous or nonexistent local times. Store and interpret timestamps deliberately. The international developer question remains useful with those distinctions.
Reference: Supported timezone catalog columns.
Related reading
- SQL in Sixty Seconds series
- Generate Script of SQL Server Objects – SQL in Sixty Seconds #184
- Prevent Unauthorized Index Modifications – SQL in Sixty Seconds #183
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
A current timezone offset is not every date offset, it is present-state information under installed rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
