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