Access TimeZoneInfo from SQL 2005 Server

Question

The .NET TimeZoneInfo class is great and I thought it would answer all my issues with recording data from multiple time zones in my SQL 2005 database.

To convert a UTC datetime in the database to any other time zone i'd just get the time zone into a TimeZoneInfo class using TimeZoneInfo.FindSystemTimeZoneById() and then call the TimeZoneInfo.ConvertTimeFromUtc(). Brilliant! I'd just call this from the SQL .NET CLR!

BUT...TimeZoneInfo has a Host Protection Attribute of MayLeakOnAbort.

When I use VS 2008 to create an SQL function or stored procedure, I cannot even see the system.TimeZoneInfo class nevermind use it. I'm assuming also that even if I could somehow reference the TimeZoneInfo class, I would probably get some sort of security exception if I tried to register the assembly in SQL Sever 2005.

Help! Is there any way to access TimeZoneInfo class and all its riches from SQL Server 2005?

NB: I've just added this caveat after the first Answer:

We have sites at different locations around the world. We need to store local time and UTC time in the database against events which may require trending at Site level. A trend may consist of over 52,000 data points over a year, so, for efficiency, I cannot just store times in UTC in the DB and convert every datapoint on the client. Thus I need the ability, within the DB to convert a local time in any timezone to and from UTC time.

Answer 1

I just finished doing this on a SQL 2008 database.

First I had to set the DB to trustworthy and verify the owner was correct.

use [myDB]
go
alter database [myDB] set trustworthy on
go

exec sp_changedbowner 'sa'
go

Next, I created a .NET solution

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Collections.ObjectModel
Imports System.Runtime.InteropServices

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub sp_ConvertTime(ByVal UTCTime As DateTime, ByVal ZoneID As String, <Out()> ByRef Output As DateTime)
    Dim sp As SqlPipe = SqlContext.Pipe

    Dim ConvertedTime As DateTime
    Dim tzUTC = TimeZoneInfo.FindSystemTimeZoneById("UTC")
    Dim tzNew = TimeZoneInfo.FindSystemTimeZoneById(ZoneID)

    ConvertedTime = TimeZoneInfo.ConvertTime(UTCTime, tzUTC, tzNew)

    Output = ConvertedTime
    sp.Send(ConvertedTime)

    ConvertedTime = Nothing
    tzUTC = Nothing
    tzNew = Nothing
    sp = Nothing

End Sub
End Class

Before deployment I set the Permission level to Unsafe.

Next I deployed it out I checked the Output window for Build errors and corrected those.

Here is the SQL Test

DECLARE @UTCTime datetime
DECLARE @ZoneID varchar(21)
DECLARE @NewTime datetime

SET @UTCTime = GETUTCDATE()
SET @ZoneID = 'Central Standard Time'

exec sp_ConvertTime @UTCTime, @ZoneID, @NewTime OUTPUT
select @NewTime AS NewTime

Answer 2

Here's a solution:

1. Create a CLR stored proc or UDF, which wraps the functionality of the TimeZoneInfo class. Follow this guide: http://www.codeproject.com/KB/cs/CLR_Stored_Procedure.aspx
2. TimeZoneInfo requires .NET 3.5. However, System.Core v3.5 will not pass verification in Sql Server 2005. So you have to do a CREATE ASSEMBLY for System.Core. See details: http://weblogs.asp.net/paulomorgado/archive/2009/06/13/playing-with-sql-server-clr-integration-part-iv-deploying-to-sql-server-2005.aspx

Note that you need to register System.Core as UNSAFE... so you DBA might have issues with it.

Furthermore, even if you were deploying to Sql Server 2008 (which includes .NET 3.5), your custom assembly would have to be UNSAFE since it uses unsafe methods from TimeZoneInfo: http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/d0515862-eb87-4a13-bab4-0e343983823a

I tried this and got a message regarding MayLeakOnAbort.

If UNSAFE is ok in you environment, you should be able to do it.

Answer 3

I don't know if you can access the TimeZoneInfo class from SQL, but it's generally considered good practice to stick to UTC in the database, with the client doing the translation into the local time.

So every time you write to the database, you translate from the local time into UTC, every time you read from the database, you translate from UTC into the local time.

EDIT: From what I understand of the problem, the solution that I would still recommend is:

1) Store the dates in UTC in the database. 2) Calculate the local time in the client.

2 can be done in a number of ways. The recommended way is to set the DateTimeMode to Local (or Utc) in the DataColumn class (*). If you need a report in local time, use Local, if you need it in UTC, use UTC.

(*) Please note that there are problems with the designer in Visual Studio, see blog post: http://bethmassi.blogspot.com/2006/01/serializing-data-across-time-zones-in.html, bug report: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=96118

Answer 4

I ran into this same problem because I wanted to convert between local and UTC in a query that was being utilized by reporting services. I went through what seems to be the same exact struggles you are going through with this. My solution...

I started out writing a stand alone app that went through the TimeZoneInfo object and wrote entries to a TimeZoneInfo table in my database. I stored all the offsets (including daylight savings offsets) for every year between a start year and end year (these were arguments to the stand alone app).

From this table, I was then able to create some sql functions that would take a date in utc or local and the timezone, use the TimeZoneInfo lookup table to get the right offset for the right time of year and timezone, and return the datetime converted to UTC or local.

Unfortunately, I still wasn't done yet. I had to create a CLR function that returned the current timezone of the system using a library that WAS safe for SQL Server (unlike the TimeZoneInfo object). I don't have access to my code at the moment, but I beleive I used the TimeZone object.

http://msdn.microsoft.com/en-us/library/system.timezone_members.aspx

To summarize, I had a CLR function that returned the system's time zone, an app that produced a timezone look up table with DLS specific information for a range of years. I topped it all off with a stored procedure that took in a timezone and a date to convert and its been working beautifully since.

I understand this is a HUGE work around to do something that seemed quite simple, but it got the job done in a safe way.

Answer 5

Have you checked out this article on Channel9? Seems to do what you are looking for in a CLR function... although I don't think you are going to get ALL of the goodies you want access to... but it is a start.

http://channel9.msdn.com/playground/Sandbox/139123/

Problem that one of the posters on that thread mentions though is that it is an unsafe assembly because of the p/invoke.

Answer 6

After fighting with the same problem for years, I finally decided to build a solution for SQL Server Time Zone Support. The project uses the standard IANA time zones, as listed here.

For example:

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles')

I realize this isn't exactly what was asked, but I think it will solve the problem equally well.

Answer 7

We've been looking for this for a while but haven't been able to find a good way to do it. I think that was on the list of things to be added in SQL 2008 if I remember from looking at it a while back.