CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
user2105237
user2105237

Reputation: 525

Getting offset of datetimeoffset in SQL Server

In SQL Server I need to find the offset of a given datetimeoffset(7).

I have researched the documentation and there are all ways to change the offset but not a way to know the offset of a particular value (sorry if I missed it).

Well I came up with the following piece of code that I find too complicated although seems to work.

DECLARE @datetimeOffset datetimeoffset(7)

SET @datetimeOffset = CAST('2007-05-08 22:35:29.1234567-05:00' AS datetimeoffset(7))

DECLARE @result datetimeoffset(7)
DECLARE @offsetMin int

SET @offsetMin = DATEDIFF(mi, cast(SWITCHOFFSET(@datetimeOffset, '+00:00') as datetime), cast(@datetimeOffset as datetime)) 

SELECT @offsetMin

I still have to do the conversion to the +00:00 format but wanted to check if there is a better way of doing this.

Thanks!

Upvotes: 39

Views: 32850

Answers (1)

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

  • 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:

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'

Upvotes: 63

Related Questions