Converting UTC to local time with SQL Server CLR


By:   |   Updated: 2011-04-07   |   Comments (7)   |   Related: More > 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":

using the surface area configuration tool for sql 22005 or the server facets for sql 2008

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.

  • build a clr module using visual studio

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

    click on the project name in solution explorer
  • Give a name to your project and click OK
  • Set up a connection to your database, test the connection and click OK

  • set up a connection to the database and test the connection
  • 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


Last Updated: 2011-04-07


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, June 13, 2017 - 8:22:03 AM - Diana Moldovan Back To Top

Hi, Kingston,

If you're using SQL 2016, consider using DATETIMEOFFSET and AT TIME ZONE. Itzik Ben Gan describes a few use cases on sqlmag

http://sqlmag.com/sql-server/what-you-need-know-about-datetimeoffset-and-sql-server-2016s-time-zone-function

Otherwise, go CLR...

 

 

 


Saturday, June 03, 2017 - 6:46:58 AM - Kingston Xavier Back To Top

 

 Hello All,

             I have a scenario that the users are using a view which includes a column of datetime datatype. Now the view is access by users across different timezones through different user interface such as Excel, Tableau etc. Now the requirement is that the time should be shown with respect to the user's timezone. Is there a way for that to get it converted to sql query ?

 

Regards,

Kingston


Thursday, May 24, 2012 - 9:02:59 AM - Diana Moldovan Back To Top

Sorry. "VS or SSMS offer great user interfaces which ARE useful....... " :)


Thursday, May 24, 2012 - 9:02:05 AM - Diana Moldovan Back To Top

Charles, thank you. VS or SSMS offer great user interfaces which is useful when learning and testing; however scripting offers more options and flexibility. Scripts are the good choice in a production environment.


Thursday, May 24, 2012 - 8:54:44 AM - Diana Moldovan Back To Top

Rod, thank you so much for outlining this risk.


Thursday, May 24, 2012 - 8:16:53 AM - Charles Back To Top

Thanks for the article and the following ones about this topic.  I want to express a deep thank you for not stopping at "just use VS build, then use VS deploy" like so many others do.  While it is true that it is "easiest", it is NOT BEST.  Deployment to your development environment should work this way and not have issues; however, deployment to larger test servers and, finally, to production servers should be done via appropriate scripts.  This allows the item to be compiled and the object code to be moved to a well-documented location.  Then, installers can use that and scripts to deploy to other servers.  This allows segregation between "development", "testing", and "production-operations" type resources.  In the end, except in the case of rather small organizations, having Visual Studio developers allowed to "deploy" straight to production from VS means that those developers have to have appropriate security to production.  I am sure others will comment about how "this should not be a problem" but it can be with regulations and laws surrounding protected information (like protected health information).  But in a more simple description, if a reusable assembly is the goal, a person can set it up so that the deployer needs to know absolutely nothing about Visual Studio.

Again, thank you deeplly for including the informaiton.  The last time (the first time) I built a CLR aggregate function, I had to sift through a number of other articles and posts to gather up deployment informaiton because the primary author of the example felt that VS deployment was "the be all and end all".

 


Monday, April 09, 2012 - 11:24:36 PM - Rod Weir Back To Top

Thanks, good article.  However, the .net TimeZoneInfo class is not recommended for CLR use in SQL Server as may leak memory.  http://msdn.microsoft.com/en-us/library/cc645949.aspx

Not sure why Microsoft doesn't just fix this!

Seems like there is no easy way to do bulk server-side date adjustments on sets of date data that is timezone and daylight saving time aware.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools