Click here to Skip to main content
Click here to Skip to main content

Calculate the UTC datetime from a Local datetime in SQL Server 2005

, 24 Feb 2014
Rate this:
Please Sign up or sign in to vote.
This tip shows how to create a UDF to convert local datetime to UTC and a way to test it

Introduction

At some point, I needed a SQL Server 2005 function to convert from a local datetime Eastern Time value to its corresponding UTC value (I wasn’t able to use the Common Language Run-time Integration in the production server). Because SQL Server 2005 doesn’t provide a built-in function to make this conversion, I spent a few hours on the internet trying to find a user defined function (UDF) able to suit my needs. I could only find a function that converts from UTC to local datetime at stackoverflow.com. Therefore, I modified that UDF to convert from local datetime to UTC as shown in this tip.

I really wanted to make sure my new UDF worked properly, so I used the CLR integration to test it.

Environment

  • SQL Server 2005 Express
  • Visual Studio Premium 2012
  • SQL Server Management Studio 11
  • Windows 7

Coding

Let’s first write the code we will use to test the UDF. We will rely on the TimeZoneInfo.ConvertTimeFromUtc .NET function to test our code.
Below is the code of a Visual Studio SQL Server Database Project, named TestUdf:

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 ConvertTimeToUtc(SqlDateTime dateUtc)
    {
        if (dateUtc.IsNull) { return dateUtc; }

        var cstZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");

        return TimeZoneInfo.ConvertTimeToUtc(dateUtc.Value, cstZone);
    }
}

After building the project, we can deploy it to SQL Server by executing the following code. Our empty database is called TimeZones (See file: Install assemblies.sql). As you can see, the Assemblies folder must be located in C: drive:

USE TimeZones
GO

ALTER DATABASE TimeZones SET TRUSTWORTHY ON;

EXEC sp_configure 'show advanced options' , '1'
GO

RECONFIGURE
GO

EXEC sp_configure 'clr enabled' , '1'
GO

RECONFIGURE
GO

EXEC sp_configure 'show advanced options' , '0';
GO

--===================================================================
-- Please restart SQL Server 2005 to settings changes to take effect
-- and then run the following code:
--===================================================================
USE TimeZones
GO

CREATE ASSEMBLY SystemCoreLib
 FROM 'C:\Assemblies\System.Core.dll'
 WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY TestAssembly 
FROM 'C:\Assemblies\TestUdf.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE FUNCTION [dbo].[clrConvertTimeToUtc](@dateUtc DateTime)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME TestAssembly.UserDefinedFunctions.ConvertTimeToUtc;
GO

You can confirm the assemblies were installed in SQL Server and the associated function was created by checking the Scalar-valued Functions and Assemblies folders out in SSMS:

Below is the table I created to test our UDF (to be created later). (See file: Create DateValues table.sql):

Now, we can populate the DateValues table (see file: Populate DateValues table.sql). Here, we will add every single day between @initialDate and @maxDate. We will also modify the hour of each day taking into account that some hours can be invalid when converting from local times to UTC:

USE TimeZones

SET NOCOUNT ON

DECLARE    @dateValue DATETIME,
           @initialDate DATETIME,
           @maxDate DATETIME,
           @hour AS TINYINT

SET @initialDate = '1950-01-01'
SET @dateValue = @initialDate
SET @maxDate = '2015-12-31'
SET @hour = 0

WHILE @dateValue < @maxDate
BEGIN
    INSERT INTO DateValues(LocalValue)
    VALUES(@dateValue)

    SET @dateValue = DATEADD(dd, 1, @dateValue)

    -- Extract date from datetime
    SET @dateValue = CONVERT(VARCHAR(10), @dateValue, 102)

    SET @hour = @hour + 1

    IF @hour > 23 SET @hour = 0

    -- Skip 2:00am in order to avoid the "DateTime invalid time" exception.
    -- More info here: http://tinyurl.com/awnsrvg 
    IF @hour = 2 SET @hour = 3

    SET @dateValue = DATEADD(hh, @hour, @dateValue)
END

DECLARE @numberOfDays SMALLINT,
    @rowCount SMALLINT

SET @numberOfDays = DATEDIFF(dd, '1950-01-01', '2015-12-31')
SELECT @rowCount = COUNT(*) FROM DateValues

IF @numberOfDays = @rowCount
    PRINT 'Table populated successfully'
ELSE
    RAISERROR(N'The table was not populated properly', 16, 1)

It’s time to use the CLR function to calculate the UTC value of the dates we inserted using the previous code:

UPDATE DateValues
SET clrValue = dbo.clrConvertTimeToUtc(LocalValue)

SELECT *
FROM DateValues 

With all the local date times and their corresponding UTC values, we can create the user defined function (UDF) based on the function found in stackoverflow (See file: ConvertTimeToUtc.sql):

-- Converts a local datetime Eastern Time value to its corresponding UTC value
CREATE FUNCTION dbo.ConvertTimeToUtc(@LocalDate AS DATETIME)
RETURNS DATETIME
AS
BEGIN
--====================================================
--Figure out the DST Offset for the Utc Datetime
--====================================================
DECLARE @DaylightSavingOffset AS SMALLINT
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--Get Year
SET @Year = YEAR(@LocalDate)

--Get First Possible DST StartDay
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
--Get DST StartDate 
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)


--Get First Possible DST EndDate
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 00:59:58.998'
ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 00:59:59.998'
--Get DST EndDate 
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)

--Get DaylightSavingOffset
SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN -1 ELSE 0 END

--====================================================
--Figure out the DST Offset 
--====================================================
DECLARE @Utc AS DATETIME

SET @Utc = DATEADD(hh, @DaylightSavingOffset, @LocalDate)

--====================================================
--Set the Timezone Offset
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = 5

--====================================================
--Finally add the Offset Datetime
--====================================================
RETURN DATEADD(hh, @Offset, @Utc)

END

GO

Now it’s time to use the UDF to update the udfValue column:

UPDATE DateValues
SET udfValue = dbo.ConvertTimeToUtc(LocalValue)

Finally, we can confirm that our UDF is working by running these queries:

SET NOCOUNT ON

SELECT COUNT(*) AS 'Number of local datetime values:'
FROM DateValues

SELECT COUNT(*) AS 'Number of rows with the same value in the CLR and UDF columns:'
FROM DateValues
WHERE clrValue = udfValue

SELECT COUNT(*) AS 'Number of rows with different values in the CLR and UDF columns:'
FROM DateValues
WHERE clrValue <> udfValue

And here are the results:

Number of local datetime values:
--------------------------------
24105

Number of rows with the same value in the CLR and UDF columns:
--------------------------------------------------------------
24105

Number of rows with different values in the CLR and UDF columns:
----------------------------------------------------------------
0

History

  • February 23, 2014 - Created the article
  • March 6, 2014 - Removed the time zone function parameter as this tip is designed only for Easter Time

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Lenin Sibaja A.

Costa Rica Costa Rica
No Biography provided
Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 24 Feb 2014
Article Copyright 2014 by Lenin Sibaja A.
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid