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 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 2

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.

Answer 3

OK, I'm getting nowhere with this! The mistake I made was providing too much detail about WHY I want to convert between time zones. Thanks to all the people that have replied - they've given me food for thought. Lots of people have comments about alternative methods but no-one seems to accept that I have good reasons to do what I'm doing and answer the question asked! I'm going to rephrase this question and resubmit as "How do I access the .NET TimeZoneInfo class from SQL Server 2005"! WITH NO OTHER DETAILS!!!

;-)

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

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.