An appointment appears twice on the fall clock change. Handling time zones starts by separating the instant from the local wall time people typed.
Store an Instant and a Business Zone
UTC is a practical storage standard for events that occurred at a specific instant. Store the UTC value and, when the business needs it, the time zone identifier that gives local meaning. An offset alone records how far from UTC a value was, but it does not carry future daylight saving rules.
I ask whether the column represents an event instant or a planned local time. A completed payment has an instant. A future 9 a.m. appointment has a local wall-time intention and a zone. Treating both as bare datetime2 values creates ambiguity during conversion.
Keep zone names under a controlled contract. SQL Server’s AT TIME ZONE uses Windows time zone names. A string such as “EST” is not enough to describe the rules. Test the name on the server that runs the conversion.
SELECT SYSUTCDATETIME() AS CurrentUtc,
SYSDATETIMEOFFSET() AS ServerLocalWithOffset;Use datetimeoffset When the Offset Matters
datetimeoffset stores date, time, and an offset. It preserves the offset associated with a value, which can be useful for an imported event. It does not by itself store a named time zone with all its historical and future rules. Keep the zone name separately when later local rendering matters.
Do not convert a datetimeoffset to datetime2 without understanding what happens to the offset. The conversion can leave a wall-time value that looks familiar while losing its relationship to UTC. Name columns clearly, such as EventUtc or EnteredLocalTime.
I inspect a sample at a daylight saving boundary before changing a type. A normal Tuesday tells you little about an ambiguous Sunday morning. Test the awkward values on purpose.
DECLARE @event datetimeoffset = '2025-11-02T01:30:00-04:00';
SELECT @event AS EnteredValue,
SWITCHOFFSET(@event, '+00:00') AS SameInstantUtc;Convert With AT TIME ZONE
AT TIME ZONE applies Windows time zone rules. A UTC datetime2 can first be marked as UTC, then converted to the desired zone. This produces a datetimeoffset value with the local time and applicable offset. It is clearer than adding a fixed number of hours.
Time zone rules can change outside SQL Server, so the result depends on the platform’s time zone data. For a report, that is usually desirable. For an audit record that must preserve how a value was originally interpreted, store the original offset and zone decision too.
I avoid hard-coded offsets in reports spanning seasons. A fixed minus five hours fails during daylight saving time in zones that change. Use the named zone and test both seasons.
DECLARE @utc datetime2(0) = '2025-07-01T12:00:00';
SELECT @utc AT TIME ZONE 'UTC'
AT TIME ZONE 'Eastern Standard Time' AS EasternLocal;
Handling Time Zones in the Spring Gap
When clocks move forward, a range of local wall times does not occur. A user can still type one of those times. SQL Server applies documented AT TIME ZONE behavior to such a value, but an appointment system must decide whether to reject, shift, or ask for clarification. Automatic conversion is not a business policy.
I show the user the interpreted time and offset before saving an ambiguous schedule. That prevents a quiet one-hour change. For completed events captured in UTC, the gap is not a problem because every UTC instant exists. The difficulty appears when starting from local wall time.
A validation procedure can compare the intended local value with a round trip through UTC and flag a change. Test it against the specific zone and year in use. Daylight rules are not identical everywhere.
SELECT CONVERT(datetime2(0), '2025-03-09T02:30:00')
AT TIME ZONE 'Eastern Standard Time' AS InterpretedLocal;Handling Time Zones in the Fall Overlap
When clocks move back, one local wall time occurs twice with different offsets. The text “1:30 a.m.” cannot identify which instant is intended. Capture the offset or ask the user to choose the first or second occurrence. A local datetime2 alone loses that distinction.
I use two datetimeoffset examples in testing, one on each side of the change. They have the same local clock display and different UTC instants. That is the kind of case a normal integration test misses until an appointment appears twice.
For grouping events by local hour, decide whether the two 1 a.m. hours should be shown separately. An operational report can need both. Use the offset in the grouping key if the distinction matters.
SELECT SWITCHOFFSET(CONVERT(datetimeoffset, '2025-11-02T01:30:00-04:00'), '+00:00') AS FirstUtc,
SWITCHOFFSET(CONVERT(datetimeoffset, '2025-11-02T01:30:00-05:00'), '+00:00') AS SecondUtc;Filter UTC Columns With UTC Boundaries
A report asking for a local day should convert the local day boundaries to UTC, then filter the indexed UTC column with a half open range. Wrapping every stored UTC value in AT TIME ZONE inside the WHERE clause can force more work. Boundary conversion is also needed because a local day can have 23 or 25 hours.
I test a report date that crosses a daylight transition. A fixed 24-hour UTC interval is wrong for that local day. Compute both local boundaries under the named zone and convert them to UTC separately. The resulting interval reflects the actual day length.
Keep the display zone explicit in the report. A date filter labeled simply “Today” can mean different intervals to readers in different regions. The query and label should agree.
DECLARE @local_start datetime2(0) = '2025-11-02T00:00:00';
DECLARE @local_end datetime2(0) = '2025-11-03T00:00:00';
SELECT SWITCHOFFSET(@local_start AT TIME ZONE 'Eastern Standard Time', '+00:00') AS UtcStart,
SWITCHOFFSET(@local_end AT TIME ZONE 'Eastern Standard Time', '+00:00') AS UtcEnd;Write a Contract for Handling Time Zones in Every Interface
State whether each timestamp field is UTC, local with a named zone, or local with an offset. Include precision and whether the value represents an instant or a planned wall time. That contract should reach files, APIs, database columns, and reports.
I check application drivers and JSON serialization too. A correct SQL column can be displayed wrongly if a client assumes the server’s local zone. Test one known instant end to end, including daylight saving dates and a reader outside the server’s region.
Handling time zones becomes manageable when each value has a declared meaning. Store instants consistently, preserve zone context when needed, and convert at a clear boundary. The clocks can disagree. The data should not.
What should happen when a local time occurs twice during a daylight-saving transition? A timestamp without an offset cannot answer that question alone. Store the instant and the relevant time-zone identifier when future local presentation matters. Test one spring transition and one fall transition for every supported zone. I keep display conversion close to the presentation boundary so storage and ordering use a consistent instant.
Related reading on this blog: Convert Date Time AT TIME ZONE and List All Available TimeZone.
A time zone is not a number of hours, it is a set of rules applied to an instant.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
