The usage of time zones in applications becomes inevitable, especially with the globalization of the applications. Reports need to be run in different time zones. Services have to consider the time zones. Applications’ users nowadays, which are around the globe use their local zones and etc.
The programming on database level also requires usage of the time zones. In this post there are some examples that will demonstrate usage of time zones.
With SQL Server 2016 you can obtain the corresponding time in another time zone from an initial time. However, the same is not that easy achievable in the lower versions of SQL Server where you’ll have to know the difference in some time units in order to determine that.
--SQL Server 2016+ versions! SELECT CONVERT(datetime2(0), getdate(), 126) AT TIME ZONE 'Korea Standard Time'; Output: ---------------------------------- 2017-08-18 12:54:00 +09:00
The above query produced the same datetime as the one in the select statement plus the “+09:00” info as the time difference with the UTC time. I can run the next query to see the datetimes and the time offset.
--SQL Server 2016+ versions! DECLARE @current_datetime DATETIME =GETDATE(); SELECT @current_datetime [Local time], CONVERT(DATETIME2, @current_datetime, 126) AT TIME ZONE 'Korea Standard Time' [Korean time], DATEDIFF(MINUTE,@current_datetime,CONVERT(DATETIME2, @current_datetime, 126)) [Time offset]; /* Output: Local time Korean time Time offset ----------------------- ---------------------------------- ----------- 2017-08-18 12:54:32.507 2017-08-18 12:54:32.5066667 +09:00 0 */
I’m trying to simply get the time offset ( 9h = 9 x 60 = 540 minutes) for the ‘Korean Standard Time’ zone, but it doesn’t work such simply because the DATEDIFF function doesn’t calculate it as expected. Additionally, the AT TIME ZONE functionality is not available in the lower versions than SQL Server 2016.
I often need the time offset from a Time Zone (e.g. ‘Korea Standard Time’) name in my calculations. In this case, I’ll have to make an additional parsing of the datetime string in order to determine that. However, not always I’ll be having the datetime in such a format (e.g. ending in +09:00).
The main question is how to determine the time offset of a given Time Zone in some time units. For that purpose, I have to use a CLR (Assembly) in SQL Server. The next query simply gives the time offsets of the listed time zones in minutes.
SELECT dbo.GetTimeOffsetForTimeZone('E. Africa Standard Time') [East Africa Time Zone]
,dbo.GetTimeOffsetForTimeZone('FLE Standard Time') [FLE Standard Time]
,dbo.GetTimeOffsetForTimeZone('Mid-Atlantic Standard Time') [Mid-Atlantic Standard Time];
/*
Output:
East Africa Time Zone FLE Standard Time Mid-Atlantic Standard Time
--------------------- ----------------- --------------------------
180 120 -120
*/I’m using a SqlTimeZone.dll (assembly) in which there is a GetTimeOffsetForTimeZone function. The function returns the time offset in minutes for a given Time Zone (TZ).
I made usage of the TimeZoneInfo class of the .NET framework in order to obtain information about all the time zones that exist. Those are written in the Operating system registry and anytime the function is called the information is retrieved from the registry. Of those reasons, the assembly in SQL Server requires to be created with the option PERMISSION_SET = UNSAFE.
When I write T-SQL code I need to sometimes know the time offset of a time zone and then I continue working with the DateTime T-SQL functions. The new AT TIME ZONE functionality does help to a certain degree, but as shown above, sometimes it’s difficult to obtain some extra information like the time offset info in minutes.
From the other side, having or keeping information for each of the time zones in a table is a little bit more difficult instead of just having a CLR for that aim. The time zones times change during the year (for some time zones, multiple times) and the table has to be updated, or the code has to be flexibly written to handle the changes of the time offsets for the Time zones. That all requires more maintenance and spending extra time.
I came to a situation where I only had the Time zone name and another thing I wanted to check was if that string is a valid time zone. Then I needed the time offset in minutes as shown above. For that purpose, I additionally added the IsValidTimeZoneName function which determines whether a Time zone name string is a valid one. The next simple query gives usage of this function.
SELECT dbo.IsValidTimeZoneName('Central Europe Standard Time') [Valid - 0/1],
dbo.IsValidTimeZoneName('Central Europe Time') [Valid - 0/1];
/*
Output:
Valid - 0/1 Valid - 0/1
----------- -----------
1 0
*/Another function that I was interested in, was the one that will make conversions for a Datetime from one time zone to a Datetime in another time zone. It’s the ConvertTimeZone function. The next query demonstrates that.
DECLARE @datum DATETIME = GETDATE(); SELECT GETDATE() [Local time], GETUTCDATE() [UTC time], dbo.ConvertTimeZone(@datum,'Central European Standard Time','SA Pacific Standard Time') [SA Pacific Standard Time], dbo.ConvertTimeZone(@datum,'Central European Standard Time','Arabic Standard Time') [Arabic Standard Time], dbo.ConvertTimeZone(GETUTCDATE(),'UTC','Korea Standard Time') [Korea Standard Time]; /* Output: Local time UTC time SA Pacific Standard Time Arabic Standard Time Korea Standard Time ----------------------- ----------------------- ------------------------- ------------------------- ------------------------- 2017-08-18 11:18:08.117 2017-08-18 09:18:08.117 8/18/2017 4:18:08 AM 8/18/2017 12:18:08 PM 8/18/2017 6:18:08 PM */
Summary and security aspects for the Assembly
In the beginning, I mentioned that the assembly must be created with PERMISSION_SET=UNSAFE because of the OS registry access to the Time zones. Due to that, the database must be set TRUSTWORTHY ON. The following code needs to be run on your instance for the assembly to be fine.
ALTER DATABASE <database_name> SET TRUSTWORTHY ON; EXEC sys.sp_configure @configname = 'clr enabled', @configvalue = 1 RECONFIGURE; ALTER ASSEMBLY [SqlTimeZone] WITH PERMISSION_SET=UNSAFE;
I presented three functions of the assembly: GetTimeOffsetForTimeZone, IsValidTimeZoneName and ConvertTimeZone. They can be used in any version of SQL Server since version 2005+. The complete DDL list of the functions and the assembly itself can be downloaded from this link. The .NET project that is done in Visual Studio 2017 is available for downloading here, so you can rebuild the project for any combination of SQL Server and .NET framework versions, in order to be able to use in your environment.