CODEWITHSUNDEEP
sql-serverdatabaset-sql
The beginner
The beginner

Reputation: 622

sys.time_zone_info's current_utc_offset in minutes format

In sys.time_zone_info of MS-SQL server current_utc_offset is stored in Hour Format(HH:MM). There is any possible way to convert that time into Minutes format? Actual Result

name                     |  current_utc_offset
---------------------------------------------------------
Syria Standard Time      |       +02:00
----------------------------------------------------------

Expected Result
name                     |  current_utc_offset        |  Minute
----------------------------------------------------------------
Syria Standard Time      |       +02:00               |     +120
----------------------------------------------------------------

Upvotes: 0

Views: 914

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

Simplest way (avoiding creative string manipulation) is to (ab)use SWITCHOFFSET:

SELECT 
    name, 
    current_utc_offset, 
    DATEPART(TZOFFSET, SWITCHOFFSET('', current_utc_offset)) AS current_utc_offset_minutes
FROM sys.time_zone_info

If your really must have the sign with that in all cases and you can't handle that client-side (which I'd consider a better option), then FORMAT is your friend:

SELECT 
    name, 
    current_utc_offset, 
    FORMAT(DATEPART(TZOFFSET, SWITCHOFFSET('', current_utc_offset)), '+0;-0') AS current_utc_offset_minutes
FROM sys.time_zone_info

There are other approaches (AT TIME ZONE could be used on the time zone name, for example).

Upvotes: 2

Related Questions