Sql-docs: Not compatible with AT TIME ZONE or listed in sys.time_zone_info

[dvansteenburg]

The value returned by CURRENT_TIMEZONE cannot be used with "AT TIME ZONE", nor is it listed in sys.time_zone_info. As such, it is not useful for converting other date's times to the instance's local time.

Example:
DECLARE @tzstr NVARCHAR(128) = current_timezone()
SELECT * FROM sys.time_zone_info WHERE name = @tzstr
SELECT @tzstr
SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC'
, (SYSUTCDATETIME() AT TIME ZONE 'UTC') AT TIME ZONE @tzstr
GO

name current_utc_offset is_currently_dst

(0 rows affected)

(UTC-06:00) Central Time (US & Canada)

(1 row affected)

Msg 9820, Level 16, State 1, Line 7
The time zone parameter '(UTC-06:00) Central Time (US & Canada)' provided to AT TIME ZONE clause is invalid.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: f3a2fcc8-dff8-e3fb-2c3c-7521b6c9798b
• Version Independent ID: a2c8ff06-a32f-79f4-d7a4-0a7f72ba3d95
• Content: CURRENT_TIMEZONE (Transact-SQL) - SQL Server
• Content Source: docs/t-sql/functions/current-timezone-transact-sql.md
• Product: sql
• Technology: t-sql
• GitHub Login: @MladjoA
• Microsoft Alias: mlandzic

[msebolt]

@dvansteenburg Thank you for the feedback.

Unfortunately there's not a standard syntax for time zones and it's on the developers to bridge these gaps.

Standardization across SQL Server makes sense, however you'll need to start a thread with the product team in the SQL Server forums andn include a link to the example above.

[TroyWitthoeft]

Unfortunately there's not a standard syntax for time zones and it's on the developers to bridge these gaps.

@msebolt Is sys.time_zone_info not the standard or close enough? I agree with @dvansteenburg . It sure would be intuitive to have CURRENT_TIMEZONE() play nice with AT TIME ZONE. Consider the common query of an analyst or developer trying to find the current time in another timezone according to SQL Server. A common practice is query such as ...

SELECT GETDATE() AT TIME ZONE ('UTC') AT TIME ZONE 'Pacific Standard Time'

This works. But here the user has to _assert_ the server's time zone. Set it in the query. How nice would it be to have the server tell us it's timezone in a canonical way using CURRENT_TIMEZONE() instead? If that function returned a value from the time zone table we could do something such as ....

SELECT GETDATE() AT TIME ZONE CURRENT_TIMEZONE() AT TIME ZONE 'Pacific Standard Time'

Which could be deployed on any SQL Server regardless of timezone. We say it's on the developer to solve this, but I feel like we are one lookup table or flag away! If there is something else I am missing, please inform me. If there is a known SQL feature request for this in the forum thread, I will take the conversation there and link back here so as not to crowd a closed thread.

Appreciate it,

• Troy Witthoeft (MCSA SQL Developer)

[MladjoA]

@dvansteenburg , @TroyWitthoeft , we've heard the feedback and will expose CURRENT_TIMEZONE_ID() soon. I will update this thread with the progress.

[MladjoA]

@dvansteenburg, @TroyWitthoeft, it's my pleasure to announce that CURRENT_TIMEZONE_ID() is now available:
https://docs.microsoft.com/en-us/sql/t-sql/functions/current-timezone-id-transact-sql

[MladjoA]

please-close