Using Time Zones in SQL Server

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.

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.

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.

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 have to be updated, or the code have 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 a usage of the this function.

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.

Summary and security aspects for the Assembly

At 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.

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.

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+

2 Replies to “Using Time Zones in SQL Server”

  1. Read this on SQLServerCentral. Good article! I have similar problems, only solutions I’ve implemented so far were very inelegant. This is clean. Could you possibly share your source? I don’t think I could implement this beyond the experimental stage without it, due to risk concerns. If not, I understand, thanks anyway for the article, it will push me in right direction.

Leave a Reply

Your email address will not be published. Required fields are marked *

*