Syntax error using 'FOR SYSTEM_TIME AS OF ... AT TIME ZONE' in SQL Server Temporal table query

Question

As example given here:

DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';

SELECT *
FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';

I get an error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'AT'

I failed to find syntax detailed explanation for 'AT TIME ZONE' statement, except for this, any idea?

SQL Server version: Microsoft SQL Server 2019 (RTM-CU23) (KB5030333) - 15.0.4335.1 (X64)

Answer 1

The documentation is (was) wrong. The incorrect information, about that AT TIME ZONE could be used, was added last year, however, due diligence wasn't performed on the PR, meaning incorrect information was added (as much as I think that the docs being open source is good, it does have it's flaws too).

Randolph West confirms this on a PR I raised:

I originally authorized the PR last year that added this change, and didn't do due diligence to test it properly beforehand.

On the WideWorldImporters database, you can do this:

DECLARE @CustomerTimeZone NVARCHAR(128) = 'Central European Standard Time';

SELECT StockItemID, ValidFrom,
    --Assign the known offset only
    ValidFrom AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST,
    --Assign the known offset, then convert to another time zone
    ValidFrom AT TIME ZONE @CustomerTimeZone AS OrderDate_TimeZoneCustomer
FROM Warehouse.StockItems;

However, you are correct, and AT TIME ZONE is not compatible with AS OF.

Instead of approving your PR directly, which removes the example entirely, I'm going to roll back the change from last year.