t-sql-at-time-zone

Integration tests exercising the T-SQL AT TIME ZONE clause against SQL Server 2022 using Testcontainers.

Prerequisites

• .NET 10 SDK (or later)
• Docker

Running Tests

dotnet test

A SQL Server 2022 container is started automatically via Testcontainers.

Test Scenarios

Scenario	Windows Time Zone	IANA Time Zone
datetime without offset — assigns offset	Eastern Standard Time	America/New_York
datetimeoffset — converts to target zone	Pacific Standard Time	America/Los_Angeles
Daylight Saving Time transitions	Eastern Standard Time	America/New_York
Chaining AT TIME ZONE	EST → PST	America/New_York → America/Los_Angeles
datetime2 with high precision	US Eastern Standard Time
smalldatetime	Eastern Standard Time
UTC to multiple zones (same instant)	EST / PST / CET	America/New_York / America/Los_Angeles / Europe/Berlin
Half-hour offset (India)	India Standard Time	Asia/Kolkata
Windows and IANA produce same result	Eastern Standard Time	America/New_York
Invalid time zone name — throws error	(invalid name)
IANA time zone when unsupported — throws error		America/New_York
Browser IANA to Windows conversion — same result	(converted from IANA)	America/Chicago
IANA to Windows ID mapping (C# only, no SQL)	Multiple	Multiple
sys.time_zone_info returns time zones	(all)

IANA time zone tests are skipped automatically when the SQL Server instance does not support IANA time zone names.

PostgreSQL Comparison

PostgreSQL supports only IANA time zone names (e.g. America/New_York, Europe/Berlin) in its AT TIME ZONE clause — Windows time zone names like Eastern Standard Time are not recognized and will produce an error. You can query pg_timezone_names to see supported names.

Browser Time Zone to SQL Server

Browsers expose the user's IANA time zone via the Intl API:

const timeZone = Intl.DateTimeFormat().resolvedOptions().timeZone;
// e.g. "America/Chicago"

In C#, convert the IANA time zone to a Windows time zone ID for use with SQL Server AT TIME ZONE:

if (TimeZoneInfo.TryConvertIanaIdToWindowsId(ianaTimeZone, out var windowsTimeZone))
{
    // Use windowsTimeZone with AT TIME ZONE in SQL Server
}

SQL Server and Azure SQL officially support only Windows time zone names (e.g. Eastern Standard Time) in AT TIME ZONE — IANA names like America/New_York are not recognized. The C# conversion above is the recommended approach when accepting IANA time zones from browser clients. The integration tests in this repository detect IANA support at runtime via sys.time_zone_info behavior and skip IANA-specific tests when the SQL Server instance does not support them.

Azure SQL

Azure SQL Database also uses Windows time zone names exclusively in AT TIME ZONE. IANA time zone names from browser clients must be converted to Windows IDs using TimeZoneInfo.TryConvertIanaIdToWindowsId before being passed to queries. You can query the supported time zones with sys.time_zone_info, which is supported on both SQL Server and Azure SQL Database:

SELECT * FROM sys.time_zone_info;

Note: Azure SQL Database always runs in UTC. Use AT TIME ZONE to convert to other time zones as needed.

Performance Patterns

AT TIME ZONE in WHERE clauses is not SARGable

Using AT TIME ZONE on an indexed column in a WHERE clause prevents SQL Server from performing an index seek. The optimizer cannot push the time zone conversion through, so it falls back to a scan:

-- ❌ Causes an index scan — AT TIME ZONE wraps the indexed column
SELECT * FROM Orders
WHERE CreatedAtUtc AT TIME ZONE 'Eastern Standard Time' > '2024-01-01 00:00:00 -05:00';

-- ✅ Convert the comparison value to UTC instead — allows an index seek on CreatedAtUtc
-- '2024-01-01 00:00:00 -05:00' (midnight Eastern) becomes '2024-01-01 05:00:00 +00:00' (UTC)
SELECT * FROM Orders
WHERE CreatedAtUtc > CAST('2024-01-01 00:00:00 -05:00' AS datetimeoffset) AT TIME ZONE 'UTC';

The general rule: keep AT TIME ZONE off the column side of the predicate and apply it to the literal/parameter side instead.

Store timestamps in UTC

Storing all timestamps in UTC (as datetimeoffset with +00:00, or as datetime2 with a documented UTC convention) avoids needing AT TIME ZONE in WHERE, JOIN, and ORDER BY clauses. Convert to the user's local time zone only at display time — either in a final SELECT projection or in the application layer:

-- Column stores UTC; convert only in the SELECT list for display
SELECT
    OrderId,
    CreatedAtUtc AT TIME ZONE 'Eastern Standard Time' AS CreatedAtLocal
FROM Orders
WHERE CreatedAtUtc > @StartUtc;

Row-by-row evaluation overhead

AT TIME ZONE is evaluated per row. On large result sets this can be noticeable. When rendering many rows for display, consider converting in the application layer (e.g. using TimeZoneInfo.ConvertTimeFromUtc in C#) to move the work off the database server.

Computed columns are non-deterministic

Because DST rules can change, AT TIME ZONE is treated as non-deterministic by SQL Server. A computed column using AT TIME ZONE can be persisted (SQL Server will re-evaluate it when the row is updated), but it cannot be indexed (no index can be created on the computed column) or used in an indexed view.

Chaining cost with datetime columns

When the source column is datetime (no offset), converting from one time zone to another requires two AT TIME ZONE calls — one to assign the source offset and one to convert:

-- Two AT TIME ZONE calls: assign Eastern offset, then convert to Pacific
SELECT CreatedAt
    AT TIME ZONE 'Eastern Standard Time'
    AT TIME ZONE 'Pacific Standard Time'
FROM Orders;

If the column were datetimeoffset (offset already present), a single AT TIME ZONE call suffices. Preferring datetimeoffset (or storing in UTC) reduces the per-row conversion cost.