DATETIMEOFFSETFROMPARTS: Build an Explicit Offset

DATETIMEOFFSETFROMPARTS constructs a timestamp using separate signed offset-hour and offset-minute components. I keep those signs consistent when building a negative offset. The supplied local clock and its offset together identify the resulting instant.

Construct the whole timestamp explicitly

This example uses a valid leap-day date, noon and 123 milliseconds. Each row varies only the offset components. The date, time and fractional precision remain explicit constructor arguments.

The resulting local-clock display is expected to remain 2024-02-29T12:00:00.123 for all five present rows. The constructor attaches the supplied offset to those components. It does not begin with a known instant that needs redisplay.

OffsetHours and OffsetMinutes are kept beside the result. Their meanings differ from the ordinary hour and minute of the local clock. Clear column names prevent those two pairs of components from being confused.

The final UtcClock column converts each constructed instant to a fixed zero offset. That is a separate comparison step. It makes the consequence of the supplied construction components visible.

WITH OffsetParts AS
(
    SELECT CaseId,OffsetHours,OffsetMinutes
    FROM (VALUES (1,5,30),(2,-3,-30),(3,0,45),(4,0,-45),(5,0,0))
        AS v(CaseId,OffsetHours,OffsetMinutes)
), Built AS
(
    SELECT *,DATETIMEOFFSETFROMPARTS(2024,2,29,12,0,0,123,
        OffsetHours,OffsetMinutes,3) AS Stamp
    FROM OffsetParts
)
SELECT CaseId,OffsetHours,OffsetMinutes,
    CONVERT(varchar(27),CAST(Stamp AS datetime2(3)),126) AS LocalClock,
    DATEPART(tzoffset,Stamp) AS CombinedMinutes,
    CONVERT(varchar(27),CAST(SWITCHOFFSET(Stamp,'+00:00') AS datetime2(3)),126) AS UtcClock
FROM Built
ORDER BY CaseId;

Apply the sign to both negative components

The first offset uses positive five hours and thirty minutes. Its expected combined offset is 330 minutes. The corresponding expected UTC clock is 06:30:00.123 on the same date.

The second uses negative three hours and negative thirty minutes. Its expected combined offset is negative 210 minutes. The corresponding expected UTC clock is 15:30:00.123.

I would not supply negative three with positive thirty to mean negative three and a half hours. The constructor expects consistent positive or negative offset components. This example intentionally executes no inconsistent-sign arguments.

The sign rule belongs to the constructor’s input contract. A display that resembles an offset string does not justify passing incompatible integers. Preserve the original parsed signs when adapting source text into components.

Keep zero-hour offsets and zero offsets distinct

The third and fourth rows set the offset hour to zero. Their minute components are positive forty-five and negative forty-five. The expected combined offsets are therefore 45 and negative 45.

Their expected UTC clocks are 11:15:00.123 and 12:45:00.123 respectively. The local clock remains noon in both. A zero hour component does not establish a zero complete offset.

The fifth row supplies zero for both offset components. Its expected UTC and local clocks agree. That is a deliberately known zero offset rather than an inferred default from a missing hour.

I include these three rows together when checking a component parser. Whole-hour samples cannot expose a discarded minute sign. The complete expected clock comparison confirms what each supplied offset actually means.

Validate calendar, fraction and missing-value rules separately

The second query supplies a missing year while keeping the precision literal three. Its expected result is NULL. The source cannot construct a complete timestamp without that required calendar component.

The constructor can report errors for invalid argument combinations. Calendar validity, offset validity and fraction precision are separate requirements. A correct sign does not make an impossible February date valid.

At precision three, the supplied fraction represents milliseconds. Changing precision without scaling that integer changes its meaning. This example keeps one explicit precision so the offset comparison remains the central variable.

The query changes no settings and creates no objects. It uses fixed offsets rather than named geographic zones. It does not discover which regional daylight-saving rule should apply to the supplied clock.

When adapting the example, preserve both component signs and the complete constructed value. Test a negative half-hour offset and a zero-hour negative offset. Those cases keep a superficially simple numeric parser from silently identifying another instant.

SELECT CONVERT(varchar(27),DATETIMEOFFSETFROMPARTS(NULL,2,29,12,0,0,123,5,30,3),126)
    AS MissingYearResult;

Test a negative half hour and a zero hour with a negative minute before you trust a parser.

A signed hour is not the complete offset, it is half of a signed pair.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.