DATEPART tzoffset: Read the Signed Offset in Minutes

DATEPART tzoffset returns signed offset minutes rather than just the hour component. I keep the unit in the result name. An offset containing part of an hour must preserve those minutes.

Read the offset as one signed quantity

The example uses explicit datetimeoffset values with the same local clock. Their offsets differ. Noon in those rows does not identify the same instant merely because the printed clock matches.

Positive five hours and thirty minutes is expected to return 330. Negative three hours and thirty minutes is expected to return negative 210. The result is an integer count of offset minutes.

I would not multiply a signed hour component and then add unsigned minutes indiscriminately. Negative offsets need the sign applied to the whole offset. The supplied typed timestamps already carry that combined meaning.

The output formats the local clock separately from SignedOffsetMinutes. That keeps the unit visible when reviewing the result. It does not split the typed input into two independent source facts.

WITH Stamps AS
(
    SELECT CaseId,Stamp
    FROM (VALUES
        (1,CAST('2026-01-15T12:00:00+05:30' AS datetimeoffset(0))),
        (2,CAST('2026-01-15T12:00:00-03:30' AS datetimeoffset(0))),
        (3,CAST('2026-01-15T12:00:00+00:00' AS datetimeoffset(0))),
        (4,CAST('2026-01-15T12:00:00+00:45' AS datetimeoffset(0))),
        (5,CAST('2026-01-15T12:00:00-00:45' AS datetimeoffset(0))),
        (6,CAST(NULL AS datetimeoffset(0)))
    ) AS v(CaseId,Stamp)
)
SELECT CaseId,CONVERT(char(19),CAST(Stamp AS datetime2(0)),126) AS LocalClock,
    DATEPART(tzoffset,Stamp) AS SignedOffsetMinutes,
    DATEPART(tzoffset,CAST(Stamp AS datetime2(0))) AS OffsetAfterDroppingType
FROM Stamps
ORDER BY CaseId;

Test offsets smaller than one hour

The fourth and fifth rows use positive and negative forty-five minutes. Their expected returned values are 45 and negative 45. The hour portion is zero, so an hour-only check would lose the difference.

These rows expose a common sign mistake in custom offset parsing. The negative sign remains meaningful even when the hour digits are zero. Returning zero for both would describe different temporal information.

The third row uses an explicit UTC offset. Its expected result is zero. This is a known offset carried by a datetimeoffset value, rather than the absence of an offset field.

I include all three cases together to make zero distinguishable from a small positive or negative offset. A test containing only whole-hour offsets can hide the missing minute component. The fixed inputs require no server-clock assumptions.

Dropping the offset changes what zero means

The last expression casts each present timestamp to datetime2 before inspecting tzoffset. Its expected result is zero for every present row. That return does not establish that the original clock was UTC.

The cast removed the original offset information. A datetime2 value contains a local-looking date and time without a stored offset. Interpreting its zero tzoffset result as a timezone discovery would invent information.

Compare the first row’s expected 330 with its expected post-cast zero. The local clock is still noon in the formatted display. The change concerns the type and its offset information, not a conversion of the instant to UTC.

I’d retain the datetimeoffset value whenever a later operation needs its original instant. A scalar extracted offset is useful metadata, but it is not the complete timestamp. Keep the date and clock as part of the temporal contract.

Keep extraction separate from time-zone lookup

The missing input produces expected NULL results. It does not supply a known zero offset. Missing timestamps deserve a separate handling policy before they enter a time-based report.

The query extracts stored fixed-offset information. It does not identify a geographic region or apply daylight-saving rules. Several named zones can share an offset at a particular moment.

This example supplies typed literals with explicit signed offsets. It changes no language, date-format or session settings. The selected datepart is written directly rather than passed as an unsupported variable keyword.

When adapting the example, retain a sub-hour negative offset in the test. Keep a known zero offset and a missing timestamp as well. Those cases prevent an integer result of zero from acquiring more meaning than its source type supports.

Name the result column in minutes so downstream code does not interpret it as hours. Keep the typed offset value beside the cast timestamp. That makes the information lost by the cast visible.

Keep the original datetimeoffset around, and a zero will never fool you.

A zero offset is not proof of UTC, it is only what the supplied type reports.

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