How to Convert UTC Time to Local Time in SQL
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
OPTION 1: Using Visual Studio
1. Write the C# Code
To write the code in Visual Studio you will need to have Visual Studio Professional or later installed.
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.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime ConvertToLocalTime(SqlDateTime utcTime)
{
if (utcTime.IsNull)
return utcTime;
else
return new SqlDateTime(utcTime.Value.ToLocalTime());
}
};
Your screen should look like the screenshot below:
xi) Save your project
2) Compile the Code
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
GO
RECONFIGURE
GO
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.
3) Test the CLR Function
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:
USE CLRExampleDatabase
GO
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.
OPTION 2: Using a Text Editor
1. Write the C# Code
i) Open any text editor of your choice and type in the following command:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime ConvertToLocalTime(SqlDateTime utcTime)
{
if (utcTime.IsNull)
return utcTime;
else
return new SqlDateTime(utcTime.Value.ToLocalTime());
}
};
ii) Save the file in an accessible folder as ConvertToLocalTime.cs. I saved this example in the following folder D:\OtherStuff\LocalTimeExample
2. Compile the Code
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.
C:\Windows\Microsoft.NET\Framework\v2.0.50727\
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
USE CLRExampleDatabase
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
iv) We can then create the assembly from the compiled DLL.
USE CLRExampleDatabase
GO
CREATE ASSEMBLY ConvertTolocalTime FROM 'C:\temp\fn_LocalTimeExample.dll' WITH PERMISSION_SET = SAFE
GO
v) Next step is to create our CLR function based on the assembly we have just created
USE CLRExampleDatabase
GO
CREATE FUNCTION ConvertTolocalTime (@Date DateTime)
RETURNS DATETIME
AS
EXTERNAL NAME ConvertTolocalTime.UserDefinedFunctions.ConvertToLocalTime
3) Test the CLR Function
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:
USE CLRExampleDatabase
GO
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.
Written By:- Adebimpe Alabi
Senior Support Engineer
Reviewed By: - Jamilu Abubakar
Escalation Engineer
Comments
Anonymous
September 21, 2011
isnt there a simple way in t-sql to do this?Anonymous
October 12, 2016
The comment has been removed