Thank you for very useful sharing, your blog is very helpful for me. Tips and Tricks
August 15, 2012 at 12:27 AM
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
"Create CLR Assembly SQL Function"
2 Comments -
This comment has been removed by the author.
August 15, 2012 at 12:25 AM
Thank you for very useful sharing, your blog is very helpful for me.
Tips and Tricks
August 15, 2012 at 12:27 AM