Question
AT TIME ZONE selects earlier offset during ambiguous DST fall-back without disambiguation option
Sora Atlas
0 reputation · 03 Nov 2022, 05:55 UTC
15.4K views1
Loading…
0 reputation · 03 Nov 2022, 05:55 UTC
SQL Server's AT TIME ZONE function converts naive datetime or datetimeoffset values to a target Windows time zone using the host OS's time zone database. During the fall-back daylight saving transition, a local clock time such as 2023-11-05 01:30 occurs twice—once in daylight time (UTC-4) and once in standard time (UTC-5). The function deterministically returns the earlier offset (standard time) and provides no parameter or hint to select the later offset.
This behavior is consistent across SQL Server 2019 and later, but the underlying Windows time zone rules can differ between OS patch levels, causing cross-environment discrepancies. Applications that require precise historical timestamps—such as audit logs or financial records—cannot rely on implicit conversion alone, because the chosen offset may not match the intended instant.
There is no built-in function to enumerate all valid offsets for a given local time in a zone, nor does AT TIME ZONE support IANA identifiers that might offer richer disambiguation metadata.
1. Detection without original offset: No. AT TIME ZONE returns a datetimeoffset with the earlier (standard-time) offset and does not expose any flag indicating that the input local time was ambiguous. Once converted, you cannot distinguish whether the source 2023-11-05 01:30 was meant as the first (daylight) or second (standard) occurrence.
2. Consistent disambiguation across Windows Server 2019/2022: Do not rely on the OS time zone database for ambiguous instants. Instead, adopt one of these patterns:
microsoftsqlserverdatetimeoffset at ingest. Capture the offset (or UTC) when the event occurs. This eliminates ambiguity entirely.tzdata release). Convert naive local times to UTC with an explicit rule (earlier/later) before writing to SQL Server.AT TIME ZONE in a deterministic function that first checks whether the target local time falls in a known ambiguous interval for that zone and OS patch level, then applies a consistent offset choice (e.g., always standard time). This still requires you to maintain the transition tables yourself.AT TIME ZONE delegates to the Windows time zone API (GetTimeZoneInformationForYear / SystemTimeToTzSpecificLocalTime). During a fall-back transition the same wall-clock time maps to two UTC instants. Windows returns the first matching offset (standard time) and provides no disambiguation parameter. SQL Server surfaces this behavior directly.
Windows time zone rules are updated via cumulative updates (KB articles). A server patched in January 2024 may have different DST transition dates for a given zone than a server patched in October 2023. Because SQL Server reads the OS database at runtime, identical T-SQL can produce different datetimeoffset results on different patch levels.
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time'; on each server and compare the offset for a known ambiguous timestamp (e.g., 2023-11-05 01:30).Get-HotFix | Where-Object {$_.HotFixID -like '*tz*'} in PowerShell.Can you change the column type or ingestion pipeline to store datetimeoffset (or UTC) at the source? If yes, that is the simplest and most robust fix.
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.