How to Convert UTC Time to Local Time in SQL

  • Article

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.

clip_image002

iii) Enter a unique name for the project. In this example I have called it LocalTimeExample2005.

iv) The window as shown below will appear.

clip_image003

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:

clip_image005

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.

clip_image007

vi) Open SSMS and check the Function and Assemblies nodes. The newly deployed CLR Function should appear.

clip_image009

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

clip_image011

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