There currently isn’t a way in T-SQL to convert UTC Time to Local time; however this seems to be a common issue people are facing. Here is how we could resolve this by writing a simple C# code and deploying it to SQL.
This can easily be achieved using 3 simple steps.
1) Write the C# Code
2) Compile the Code
3) Test the CLR Function
I have split this into 2 options, Option 1 is for users of Visual Studio and Option 2 is for other users who will be using a text editor. Using Visual Studio is the easier option but never mind; I have pasted all the code you will need for this to work. J
i) Open Visual Studio
ii) Click on File -> New -> Project -> SQL Server Project.
iii) Enter a unique name for the project. In this example I have called it LocalTimeExample2005.
iv) The window as shown below will appear.
v) Select the Server name and the database name.
vi) The database name in this example is CLRExampleDatabase
vii) Test your connection to ensure it works.
viii) Click OK.
ix) On the navigation bar click on Project -> Add User Defined Function
x) Once the window is open, type in the following command.
public partial class UserDefinedFunctions
public static SqlDateTime ConvertToLocalTime(SqlDateTime utcTime)
return new SqlDateTime(utcTime.Value.ToLocalTime());
Your screen should look like the screenshot below:
xi) Save your project
i) Make sure you SQL Server is running.
ii) You will need to ensure you have enabled CLR from running on the server. This option is disabled by default by running the following script on the database, as my database USE CLRExampleDatabase GO
sp_configure 'clr enabled', 1
iii) On the Navigation bar click on Build.
iv) Select the Deploy option
v) “Deploy Succeeded“ would appear at the bottom left hard corner as shown below.
vi) Open SSMS and check the Function and Assemblies nodes. The newly deployed CLR Function should appear.
Once the CLR function created has been compiled, we will need to test it to make sure it working as expected. To do this open SSMS and run the following commands:
SELECT dbo.ConvertToLocalTime('2010-05-01 10:00')
SELECT dbo.ConvertToLocalTime('2010-12-01 10:00')
The results would show the hour ahead in May because we are in BST and the same time in December because we are back to GMT.
i) Open any text editor of your choice and type in the following command:
ii) Save the file in an accessible folder as ConvertToLocalTime.cs. I saved this example in the following folder D:\OtherStuff\LocalTimeExample
To compile the code you need to make sure the CLR Function you have created in Visual Studio has been saved in an accessible folder. This example is using the csc.exe which is a C# Compiler. This is found the .Net framework folder. To do this you will need to follow these steps:
i) Navigate to the .Net framework folder. This is usually located in the following location.
ii) Type the following:
csc /target:library /out:c:\temp\fn_LocalTimeExample.dll D:\OtherStuff\LocalTimeExample\LocalTimeExample\ConvertToLocalTime.cs
iii) After compiling the CLR successfully, we need to enable CLR on the database. Open SSMS and run the following script
iv) We can then create the assembly from the compiled DLL.
CREATE ASSEMBLY ConvertTolocalTime FROM 'C:\temp\fn_LocalTimeExample.dll' WITH PERMISSION_SET = SAFE
v) Next step is to create our CLR function based on the assembly we have just created
CREATE FUNCTION ConvertTolocalTime (@Date DateTime)
EXTERNAL NAME ConvertTolocalTime.UserDefinedFunctions.ConvertToLocalTime
Written By:- Adebimpe Alabi
Senior Support Engineer
Reviewed By: - Jamilu Abubakar
isnt there a simple way in t-sql to do this?