Write a SQL Server query that returns orders for one local business date when timestamps are stored in UTC.

Table: Orders(OrderId, CreatedAtUtc, Status), where CreatedAtUtc is datetimeoffset in UTC. Parameters are @LocalDate and a validated Windows time-zone name such as Central European Standard Time. Return orders whose local timestamp falls on that date.

A daylight-saving transition day may contain 23 or 25 hours. Converting midnight at the start and next midnight independently produces the correct UTC interval instead of assuming every local day is 24 hours.

• Validate the time-zone identifier before the query runs.
• Use an inclusive UTC start and exclusive UTC end.
• Do not apply time-zone conversion to every stored row.
• Keep CreatedAtUtc consistently stored as UTC.

I create local midnight for the requested date and the next date, attach the requested time zone to each boundary, then convert both boundaries to UTC. The final predicate compares the unchanged indexed column against constants. Computing both boundaries independently handles daylight-saving transitions correctly.

DECLARE @LocalStart datetime2 = CONVERT(datetime2, @LocalDate);
DECLARE @LocalEnd datetime2 = DATEADD(day, 1, @LocalStart);

DECLARE @UtcStart datetimeoffset =
    (@LocalStart AT TIME ZONE @WindowsTimeZone) AT TIME ZONE 'UTC';

DECLARE @UtcEnd datetimeoffset =
    (@LocalEnd AT TIME ZONE @WindowsTimeZone) AT TIME ZONE 'UTC';

SELECT o.OrderId, o.CreatedAtUtc, o.Status
FROM dbo.Orders AS o
WHERE o.CreatedAtUtc >= @UtcStart
  AND o.CreatedAtUtc < @UtcEnd
ORDER BY o.CreatedAtUtc, o.OrderId;

The final range can seek on an index beginning with CreatedAtUtc. Converting every row with AT TIME ZONE or CONVERT(date, ...) would add CPU work and often prevent a simple seek. Boundary conversion is constant work per request.

• The spring-forward day with 23 local hours.
• The fall-back day with 25 local hours.
• Orders exactly at local midnight boundaries.
• An invalid or unsupported time-zone identifier.

• Test dates on both daylight-saving transitions for the chosen zone.
• Place one order exactly at each UTC boundary.
• Compare the execution plan with a function applied to CreatedAtUtc.
• Verify the same UTC event may belong to different local dates in different zones.

• Assuming a local day is always exactly 24 hours.
• Converting every stored timestamp inside the predicate.
• Using an inclusive end boundary.
• Accepting arbitrary time-zone text without validation or error handling.