Converting UTC to local time with SQL Server CLR

By: Diana Moldovan   |   Updated: 2011-04-07   |   Comments (7)   |   Related: > Dates

Problem

Suppose that your company uses an international ERP application which stores the date and time data as UTC, not as local time. Usually the conversion to the local time is handled at the application level. But this time you need a custom data export to another in house application and the date and time data should be expressed as local time. In this tip I'll explain a straightforward way to convert the UTC date and time to local time using a CLR scalar function.

Solution

To get this job done, I could use the DATEADD function and add the time offset to the UTC data. In my case the time offset today, March 25th, is +2 hours, since I am located at the EET ("GTB Standard Time") time zone. So, on SQL 2005 I can use this:

DECLARE @d DATETIME;
SET @d = '2011-02-27 19:38:27.990'
SELECT DATEADD(hour, 2, @d) AS LocalTime

You can also use DATEADD with the new SQL 2008 date and time data types. However I have one more issue to be aware of, and this is the "daylight savings time", which has to be taken into consideration for the historical data. Each year, between the last Sunday in the month of March (this year March 27th) and the last Sunday in the month of October, I have to add an extra hour for the daylight savings time. Therefore the code should be:

DECLARE @d DATETIME;
SET @d = '2011-03-30 19:38:27.990'
SELECT DATEADD(hour, 3, @d) AS LocalTime

And consider also that this daylight savings time rule is country dependent...

SQL 2008 offers the DATETIMEOFFSET data type, which contains the time offset information and the SWITCHOFFSET function, which changes the time offset. While this is a significant step forward, DATETIMEOFFSET is still not aware of the daylight savings time or of the time zone. It only stores the offset at the time of storage. If I worked with SQL 2008 and my temporal data were stored as DATETIMEOFFSET, I could re-write the code like this:

DECLARE @d DATETIMEOFFSET;
SET @d = '2011-02-27 19:38:52.1213549 +00:00'
SELECT SWITCHOFFSET(@d, '+02:00')  AS LocalTime

And like this for daylight savings time:

DECLARE @d DATETIMEOFFSET;
SET @d = '2011-03-30 19:38:52.1213549 +00:00'
SELECT SWITCHOFFSET(@d, '+03:00')  AS LocalTime

The daylight savings adjustment still has to be managed separately.

Keep in mind, though, that SQL 2008 offers the new SYSDATETIME, SYSUTCDATETIME and SYSDATETIMEOFFSET built-in data types, which are daylight savings time aware based on the operating system settings.

Fortunately .NET offers classes such as TimeZone and TimeZoneInfo which include support for converting between UTC and local time. And it was easy enough to build a CLR scalar function to return the local time required.

To be able to run user CLR code, you must first enable its execution, either by running:

sp_configure ‘clr enabled', 1  
GO  
RECONFIGURE  
GO

or using the "surface area configuration" tool for SQL 2005 or the "server facets" for SQL 2008 - right click on the server name and choose "Facets":

The most straightforward way to build CLR modules is to use Visual Studio. Since I used Visual Studio 2010 and things have changed since the last versions, let me outline the process:

• Open VS 2010 and click on "New Project"

• Choose the Database ---> SQL Server ---> Visual C# SQL CLR Database Project template. Notice the prerequisite written on the right side of the screen. SQL Server 2005 and SQL Server 2008 require that SQL CLR assemblies target version 2.0, 3.0, or 3.5 of the .NET framework.



To see which .NET version your projects targets, right click on the project name in Solution Explorer and choose Properties.


• Give a name to your project and click OK

• Set up a connection to your database, test the connection and click OK



• Right click on the project and add a user defined function.

• In my case the code is very simple. It takes as input a datetime column which contains UTC times and returns the corresponding local time:

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 ToLocalTime(SqlDateTime dt)
    {
        try
        {
             return TimeZone.CurrentTimeZone.ToLocalTime(dt.Value);
        }
        catch
        {
            return SqlDateTime.Null;
        }
    }
}

I'm using here the TimeZone.CurrentTimeZone.ToLocalTime method, which returns the local time that corresponds to a specified date and time value. Since the input has to be a DateTime (not a SqlDateTime), I pass as input the "value" of the dt parameter, which is a DateTime.

• For a .NET developer, the easiest way to create the assembly and the function into the underlying database (at least on the development environment) is to choose the "Deploy" option from the "Build" menu. Furthermore, if you decide to change the .NET code, you'll only need to build and deploy the project again. Visual Studio will drop and re-create the assembly and the function for you behind the scenes. However, here is the TSQL code you'll need when you move the assembly to production:

CREATE ASSEMBLY MSSQLTIPS_ToLocalTime
AUTHORIZATION [dbo]
FROM 'Your_Location\MSSQLTIPSToLocalTime.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[ToLocalTime](@dt [datetime])
RETURNS [datetime] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MSSQLTIPS_ToLocalTime].[UserDefinedFunctions].[ToLocalTime]
GO

MSSQLTIPSToLocalTime.dll is the dll you obtained by building the Visual Studio project.

• Let's create a sample table and test the function:

CREATE TABLE [dbo].[DTTest](
        [row_id] int IDENTITY(1,1) PRIMARY KEY,
        [UTCTime] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO DTTest VALUES('2010-07-23 21:16:52'), ('2011-03-23 21:16:52'),
(NULL)
GO
SELECT row_id, UTCTime, dbo.ToLocalTime(UTCTime) AS LocalTime FROM DTTest

• Notice that the function adds the correct time offset for each month - i.e. 2 hours for February, 3 hours for July. If the input is NULL, the function will return a NULL result.

• If you work on SQL 2008, you'll likely need a function which reads and returns for example datetime2 data. Sqldatetime "maps" to datetime, not to datetime2. The code below tries to create a function which reads and returns datetime2 from MSSQLTIPSToLocalTime.dll and will fail because "T-SQL and CLR types for return value do not match".

  CREATE FUNCTION [dbo].[ToLocalTime_dt2](@dt [datetime2])
  RETURNS [datetime2] WITH EXECUTE AS CALLER
  AS 
  EXTERNAL NAME [MSSQLTIPS_ToLocalTime].[UserDefinedFunctions].[ToLocalTime]
  

  You'll have to change the .NET code so that the function's signature contains DateTime, not Sqldatetime:

  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 DateTime ToLocalTime_dt2(DateTime dt)
      {
          try
          {
              return TimeZone.CurrentTimeZone.ToLocalTime(dt);
          }
          catch
          {
              return DateTime.Parse("1900/01/01");
          }
      }
      };
  

Next Steps

• In this very simple case I could have used the DateTime.ToLocalTime() method. However, for more accurate results and conversions from one time zone to another (not only between local and UTC), .NET 3.5 introduces the sophisticated TimeZoneInfo class.
• Find other ways to benefit from CLR integration

About the author

Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips