- The
DATEPART
function has atz
option which returns the value’s UTC offset inint
minutes. DATENAME
also supports thetz
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 useDATEPART
/DATENAME
to get the timezone name.
- Note that a
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:
Zone | Value | OffsetMinutes | OffsetHHMM |
---|---|---|---|
'US Pacific' |
2022-07-24 12:34:56.0000000 -07:00 |
-420 |
'-07:00' |
'UTC' |
2022-07-24 19:34:56.0000000 +00:00 |
0 |
'+00:00' |
'Australian Eastern Standard Time' |
2022-07-25 05:34:56.0000000 +10:00 |
600 |
'+10:00' |