Querying Local Dates Against UTC Data in SQL Server

A report for "orders placed on Sunday" is incomplete until somebody defines whose Sunday it means. A UTC timestamp identifies an instant, but the calendar date shown to a customer depends on the customer's time zone. The most common mistake is to apply a fixed offset or add 24 hours to a converted boundary. Both can produce plausible totals while silently losing or including orders around a daylight saving transition.

A useful storage convention is a datetime2 column whose contract explicitly says UTC, with that convention reflected in its name. datetime2 does not store a time zone or enforce UTC. Every writer must supply the right value. datetimeoffset retains an offset, but an offset alone is still not a named time zone with historical and future transition rules.

Convert the boundaries once

Suppose the business wants the local date March 10, 2024 in Denver. Create the two local midnights first, then convert each independently to UTC. This example uses the Windows time zone identifier available on the SQL Server instance; inspect sys.time_zone_info before adopting identifiers in an application.

DECLARE @LocalDate date = '20240310';
DECLARE @Zone sysname = N'Mountain Standard Time';
DECLARE @StartLocal datetime2(0) = CONVERT(datetime2(0), @LocalDate);
DECLARE @EndLocal datetime2(0) = DATEADD(day, 1, @StartLocal);
DECLARE @StartUtc datetime2(0) = CONVERT(datetime2(0),
    @StartLocal AT TIME ZONE @Zone AT TIME ZONE 'UTC');
DECLARE @EndUtc datetime2(0) = CONVERT(datetime2(0),
    @EndLocal AT TIME ZONE @Zone AT TIME ZONE 'UTC');
SELECT @StartUtc AS StartUtc, @EndUtc AS EndUtc,
       DATEDIFF(hour, @StartUtc, @EndUtc) AS HoursInLocalDay;

The expected UTC boundaries are March 10 at 07:00 and March 11 at 06:00. The local day lasts 23 hours. The identifier contains "Standard Time", but the zone includes daylight saving rules. For the November 3, 2024 local date, the corresponding interval lasts 25 hours.

There are two conversions in each expression. Applying AT TIME ZONE to an offset-free datetime2 interprets that value as local time in the supplied zone. Applying it again to the resulting datetimeoffset converts the instant to UTC. Only after that conversion is it safe to discard the now-zero offset for comparison with a UTC datetime2 column.

For arbitrary zones and dates, midnight itself can be affected by historical rule changes. If your application accepts local appointment times, define what it does with nonexistent or ambiguous times. A database function's default resolution is not necessarily the business policy. Persist the chosen instant and enough original context to explain that choice.

Use a half-open interval

The production query should compare the stored column directly with the precomputed boundaries.

-- Assumes Orders.OrderedAtUtc contains UTC datetime2 values.
SELECT OrderId, OrderedAtUtc, Total
FROM dbo.Orders
WHERE OrderedAtUtc >= @StartUtc
  AND OrderedAtUtc < @EndUtc;

The lower boundary is inclusive and the upper boundary is exclusive. An order exactly at the following midnight belongs to the next day. This remains correct for datetime2 precision changes and avoids inventing a final time such as 23:59:59.997, which is tied to assumptions about a particular data type.

An index beginning with OrderedAtUtc can support this range. If every query is also restricted to one tenant, an index beginning with TenantId followed by OrderedAtUtc may fit better. Decide from the real predicates and workload. Converting every stored timestamp to local time inside WHERE typically prevents a straightforward seek on the original timestamp index and repeats conversion work for many rows.

Keep parameter types aligned with the column. If existing data uses datetimeoffset, preserve that type in the boundaries instead of copying this datetime2 contract blindly. Also validate that the requested end date follows the start date and limit unexpectedly large reporting ranges.

Preserve the meaning of time

Do not mix UTC values and server-local values in the same column. A later conversion cannot reliably identify which convention each row followed, particularly during the repeated hour in autumn. Audit ingestion paths, imports, and default constraints before changing reporting logic.

A future meeting at 09:00 local time is a different problem from recording when an order occurred. Future legal time zone rules can change. Depending on the product promise, store the intended local date and time plus the named zone, and decide when its UTC instant is recalculated. For completed events, retain the actual instant.

Test ordinary days, both daylight saving transitions, rows exactly on each boundary, and tenants in different zones. Compare the selected order identifiers, not only totals: two boundary mistakes can cancel out numerically. Correct temporal filtering is a data contract supported by a query, not just a clever conversion expression.

Technical references: Microsoft Learn: AT TIME ZONE · Microsoft Learn: datetime2.