Thursday, June 7, 2012

Create CLR Assembly SQL Function

Create a cs code file with the desired functionality. Example:
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class TimeZoneHelper
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime MountainToUtc(DateTime mountainDateTime)
    {
         TimeZoneInfo zone = TimeZoneInfo.FindSystemTimeZoneById("Mountain Standard Time"); // "Mountain Standard Time" it's the ID regardless of daylight saving time (i.e. no matters if right now is daylight saving time, the ID name remains the same)

         DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(mountainDateTime, zone); // it automatically takes care of daylight saving time

         return utcTime;
    }
      
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime PacificToUtc(DateTime pacificDateTime)
    {
         TimeZoneInfo zone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time"); // "Pacific Standard Time" it's the ID regardless of daylight saving time (i.e. no matters if right now is daylight saving time, the ID name remains the same)

         DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(pacificDateTime, zone); // it automatically takes care of daylight saving time

         return utcTime;
    }
}
Save the file as TimeZoneHelper.cs
Open the Visual Studio 2008 Command Prompt (I also tried the 2010 version but it didnt work) and run the following:
csc /target:library C:\Users\jon.connor\Downloads\TimeZoneHelper.cs
This will create a TimeZoneHelper.dll in the same directory where the source file is in.
In SQL Server run the following to enable CLR code to run:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

ALTER DATABASE Northwind SET TRUSTWORTHY ON;
GO
You also have to grant permissions to a SQL Server login:
GRANT UNSAFE ASSEMBLY TO some_user;
GO
Now you can create the assembly in the Northwind database
CREATE ASSEMBLY TimeZoneHelperAssembly 
FROM 'C:\Users\jon.connor\Downloads\TimeZoneHelper.dll' 
WITH PERMISSION_SET = UNSAFE
GO
Finally create functions based on the assembly methods.
CREATE FUNCTION dbo.MountainToUTC(@mountainDateTime datetime) 
RETURNS datetime 
AS EXTERNAL NAME 
[TimeZoneHelperAssembly].[TimeZoneHelper].[MountainToUtc]
GO

CREATE FUNCTION dbo.PacificToUTC(@pacificDateTime datetime) 
RETURNS datetime 
AS EXTERNAL NAME 
[TimeZoneHelperAssembly].[TimeZoneHelper].[PacificToUtc]
GO

And now you can start using these functions as you would normally do:
select dbo.MountainToUTC('2012-04-13 16:00:00'), 
       dbo.PacificToUTC('2012-04-13 16:00:00')


Thanks to http://goo.gl/W4u42