CODEWITHSUNDEEP
sqlsql-servertimezone
K M
K M

Reputation: 125

How to SELECT the UTC offset from a DateTimeOffset object?

I am currently using: 

SELECT DATEPART(TZ, SYSDATETIMEOFFSET())

However, it returns the offset in minutes. I would like to preserve the format of '-05:00' instead of '-300'

Thank you.

Upvotes: 6

Views: 13248

Answers (3)

jacoviza
jacoviza

Reputation: 1008

You can also use the FORMAT method to get straight to the offset piece like:

SELECT FORMAT(SYSDATETIMEOFFSET(), 'zzz') AS TimezoneOffset

Upvotes: 0

Wowe
Wowe

Reputation: 502

In MS SQL Server you can also use

SELECT DATENAME(tz, SYSDATETIMEOFFSET())

which will return a nvarchar with the offset

screenshot of the excecution result

Upvotes: 10

Joe Alfano
Joe Alfano

Reputation: 10299

If you want to extract the exact string '-05:00' at the end of a datetimeoffset variable, you can use SQL Server string manipulation. I do not think it is possible to do this using the built-in SQL DateTime functions. You can use the CAST function, which I believe defaults to ISO 8601 format:

declare @timeStr nvarchar(50) = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
select right(@timeStr, 6)

If you want to be more explicit, you can use the CONVERT function with a format type of 126, explicitly telling SQL Server to use ISO 8601:

declare @timeStr nvarchar(50) = CONVERT(nvarchar(50), SYSDATETIMEOFFSET(), 126)
select right(@timeStr, 6)

Both of these approaches in my time zone return:

-06:00

For more information about CAST and CONVERT see here.

Upvotes: 5

Related Questions