SQL: Converting a TimeZoneOffset to Hours

I’ve found the sys.time_zone_info system view that was added in SQL Server 2016, really useful in a number of situations. It returns the name of each timezone, along with their current offset from UTC (like +01:00 or -07:00 ) and if they are or are not currently in daylight savings time.

However, what I’ve found myself needing to do is to convert the timezone offset part to a decimal number of hours.

The TimeZoneOffsetToHours function in our free SDU Tools for developers and DBAs does just that. It takes a single parameter, which is:

@TimezoneOffset nvarchar(20) - that’s the value returned by the sys.time_zone_info view.

It returns a decimal number of hours. The returned data type is decimal(18, 2). Note these are decimal hours returned, not minutes.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

https://youtu.be/2JRKZeNEIrE

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE OR ALTER FUNCTION SDU_Tools.TimezoneOffsetToHours
(
    @TimezoneOffset nvarchar(20)
)
RETURNS decimal(18,2) 
AS
BEGIN
-- Function:      Calculates a number of hours from a timezone offset
-- Parameters:    @TimezoneOffset nvarchar(20) => as returned by sys.time_zone_info
-- Action:        Calculates a number of hours from a timezone offset
-- Return:        decimal(18,2)
-- Refer to this video: https://youtu.be/2JRKZeNEIrE
--
-- Test examples: 
/*

SELECT SDU_Tools.TimezoneOffsetToHours(N'-11:30');

*/
    RETURN CASE WHEN LEFT(@TimezoneOffset, 1) = N'+' THEN 1 ELSE -1 END -- sign
           * (
                 CAST(SUBSTRING(@TimezoneOffset, 2, CHARINDEX(N':', @TimezoneOffset) - 2) AS decimal(18,2)) -- hours
                 + CAST(SUBSTRING(@TimezoneOffset, CHARINDEX(N':', @TimezoneOffset) + 1, 20) AS decimal(18,2)) / 60.0 -- minutes
             ); 
END;
GO

2020-05-27