Getting offset of datetimeoffset in SQL Server

• The DATEPART function has a tz option which returns the value’s UTC offset in int minutes.
• DATENAME also supports the tz option and returns the formatted UTC offset in the form [+-]HH:MM
  • Note that a datetimeoffset value only contains a scalar UTC offset value, it does not contain any time-zone information at all (as multiple timezones can and do share the same offset), so it is not possible to use DATEPART/DATENAME to get the timezone name.

DECLARE @dateTimeOffsetValue_USPacific datetimeoffset(7) = '2022-07-24 12:34:56-07:00';
DECLARE @dateTimeOffsetValue_UTC       datetimeoffset(7) = '2022-07-24 19:34:56Z';
DECLARE @dateTimeOffsetValue_Bendigo   datetimeoffset(7) = '2022-07-25 05:34:56+10:00';

SELECT
    'US Pacific' AS "Zone",
    @dateTimeOffsetValue_USPacific AS "Value",
    DATEPART( tzoffset, @dateTimeOffsetValue_USPacific ) AS OffsetMinutes,
    DATENAME( tzoffset, @dateTimeOffsetValue_USPacific ) AS OffsetHHMM

UNION ALL

SELECT
    'UTC' AS "Zone",
    @dateTimeOffsetValue_UTC AS "Value",
    DATEPART( tzoffset, @dateTimeOffsetValue_UTC ) AS OffsetMinutes,
    DATENAME( tzoffset, @dateTimeOffsetValue_UTC ) AS OffsetHHMM

UNION ALL

SELECT
    'Australian Eastern Standard Time' AS "Zone",
    @dateTimeOffsetValue_Bendigo AS "Value",
    DATEPART( tzoffset, @dateTimeOffsetValue_Bendigo ) AS OffsetMinutes,
    DATENAME( tzoffset, @dateTimeOffsetValue_Bendigo ) AS OffsetHHMM;

Gives this result: