Thursday, December 25, 2008

Time Zone ability for SQL Server 2005 and 2008

Few months ago we needed Time Zone functionality for one of our projects. This project is implemented using .NET 2.0 and SQL Server 2005. And as we know, there is no time zone functionality in either of the them. After doing some search, one of my developers  stumpled across this article on code project http://www.codeproject.com/KB/dotnet/WorldClock.aspx. This article describes how to use Windows built in timezone functionality. We used this approch to implement timezone functionality. Next we needed the same functionality in SQL Server. Luckily SQL Server 2005 and up supports .NET CLR. We used the same classes and created .NET CLR functions (http://msdn.microsoft.com/en-us/library/w2kae45k(VS.80).aspx). As this approch makes use of unsafe code, assembly needs to be attached using the unsafe option. By default, this option will not work. SQL Server Database has to be set trusworthy inorder to attach an unsafe assembly.

Hope this helps.

No comments: