Creating the Timezone function as a C# CLR function

In revisiting a previous process I had written to provide timezone conversion functions in MS SQL I decided to look at ways to potentially improve the performance of the function.  I had discovered certain performance issues with large queries that I was hoping to improve.

This post is an extension of a previous post that will have several other detailed posts associated to it.

One thing to note is that there are many ways to provide this functionality.  First was that my design principles were to provide functions that are similar to use as the “NEW_TIME” function that Oracle has though with one addition, I did not want the user to have to know whether to provide a code for daylight savings or for standard.  I also did not want the user to have to provide a lot of inputs to the function to make it easy to use.

In looking at this process I was wondering if a C# CLR function might perform better since it was compiled code.  I will provide my findings on whether this did improve performance or not in a later post.  This post will focus primarily on just creating the C# CLR function and how to register it in MS SQL.

Another thing to note is that since this is my first time creating a CLR function it may be that I am not using best practices.  Also I kept the logic of this process similar to the SQL version previously published.

I have an administrative class that I use to manage the timezone parms needed for these functions.  The class definition is listed below.

public class TimeZoneParm

{

    private string timeZoneCd;

    private string timeZoneName;

    private int offSetHr;

    private int offSetMi;

    private int dstOffSetHr;

    private int dstOffSetMi;

    private string dstEffDt;

    private string dstEndDt;

    public TimeZoneParm()

    {

    }

    public string TimeZoneCd

    {

        get { return timeZoneCd; }

        set { timeZoneCd = value; }

    }

    public string TimeZoneName

    {

        get { return timeZoneName; }

        set { timeZoneName = value; }

    }

    public int OffSetHr

    {

        get { return offSetHr; }

        set { offSetHr = value; }

    }

    public int OffSetMi

    {

        get { return offSetMi; }

        set { offSetMi = value; }

    }

    public int DstOffSetHr

    {

        get { return dstOffSetHr; }

        set { dstOffSetHr = value; }

    }

    public int DstOffSetMi

    {

        get { return dstOffSetMi; }

        set { dstOffSetMi = value; }

    }

    public string DstEffDt

    {

        get { return dstEffDt; }

        set { dstEffDt = value; }

    }

    public string DstEndDt

    {

        get { return dstEndDt; }

        set { dstEndDt = value; }

    }

}

First off I created the function for converting time to UTC.  It is true that recent versions of MS SQL provides a new function, getutctime(), that can provide UTC time but it only helps in providing the current UTC time based upon the current server time.  You are not able to provide a custom date/time value from a different timezone to convert it to UTC.

I will provide all the code in one window but will describe each part within the code.  First off if you are not familiar with function declarations.  Function declarations are descriptors you can add to the function to tell the CLR how to handle the function.  This is not required though it is good to get in the habit of doing this since it makes your code more user friendly.  The descriptors that I added was to tell the CLR that this function is read only, deterministic (always provides the same results with the same parms) and the name that should be used for the function in MS SQL.

This function only takes two inputs, date/time to be converted and the timezone code that represents the timezone that the value is in.  The function returns a SqlDataTime data type value.

The first part of the function creates and instantiates variables and then creates a SQL statement and connection string to pull the values from the time_zone table (code and values can be found in the previous post).  The code the executes the code and populates the variables.  Next the code parses the DST start and end times to see if the date falls within the DST range (for more information on the values being used and the logic behind it check out the previous post for the SQL solution).

The function then figures out what the converted date/time would be with and without the DST modifier.  Then comes a logic step that checks to see which value should be used and then returns the appropriate value.  As with good coding practices all of this is surrounded by a try/catch framework and if there is an error the function will return the original value.

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "CLR_UTC_TIME")]

        public static SqlDateTime fnUtcTime(SqlDateTime OrigDate, SqlString CurrentTimeZone)

        {

            try

            {

                int offsethr = 0;

                int offsetmi = 0;

                int dst_offsethr = 0;

                int dst_offsetmi = 0;

                string dst_eff_key = "01110000";

                string dst_end_key = "01110000";

                string cmdSelectText = "SELECT " +

                            "offset_hr, " +

                            "offset_mi, " +

                            "dst_offset_hr, " +

                            "dst_offset_mi, " +

                            "dst_eff_dt, " +

                            "dst_end_dt " +

                        "FROM time_zones " +

                        "WHERE timezone_cd = '" + CurrentTimeZone.ToString() + "' AND " +

                            "CONVERT(DATETIME,'" + OrigDate.ToString() + "') BETWEEN eff_dt AND end_dt";

                using (SqlConnection conn = new SqlConnection("context connection=true"))

                {

                    conn.Open();

                    SqlCommand cmd = new SqlCommand(cmdSelectText, conn);

                    SqlDataReader sdr = cmd.ExecuteReader();

                    while (sdr.Read())

                    {

                        offsethr = int.Parse(sdr["offset_hr"].ToString());

                        offsetmi = int.Parse(sdr["offset_mi"].ToString());

                        dst_offsethr = int.Parse(sdr["dst_offset_hr"].ToString());

                        dst_offsetmi = int.Parse(sdr["dst_offset_mi"].ToString());

                        dst_eff_key = sdr["dst_eff_dt"].ToString();

                        dst_end_key = sdr["dst_end_dt"].ToString();

                    }

                    conn.Close();

                }

                DateTime origDate = DateTime.Parse(OrigDate.ToString());

                int dst_eff_mon = int.Parse(dst_eff_key.Substring(0, 2));

                int dst_eff_wk = int.Parse(dst_eff_key.Substring(2, 1));

                int dst_eff_dw = int.Parse(dst_eff_key.Substring(3, 1));

                int dst_eff_hr = int.Parse(dst_eff_key.Substring(4, 2));

                int dst_eff_mi = int.Parse(dst_eff_key.Substring(6, 2));

                int dst_end_mon = int.Parse(dst_end_key.Substring(0, 2));

                int dst_end_wk = int.Parse(dst_end_key.Substring(2, 1));

                int dst_end_dw = int.Parse(dst_end_key.Substring(3, 1));

                int dst_end_hr = int.Parse(dst_end_key.Substring(4, 2));

                int dst_end_mi = int.Parse(dst_end_key.Substring(6, 2));

                DateTime dst_eff_dt_test = new DateTime(origDate.Year, dst_eff_mon, 1);

                dst_eff_dt_test = dst_eff_dt_test.AddDays((double)(-1 * DayOfWeekId(dst_eff_dt_test.DayOfWeek)));

                DateTime dst_eff_dt = dst_eff_dt_test.AddDays(dst_eff_wk * 7);

                dst_eff_dt = dst_eff_dt.AddHours(dst_eff_hr);

                dst_eff_dt = dst_eff_dt.AddMinutes(dst_eff_mi);

                dst_eff_dt = dst_eff_dt.AddDays(dst_eff_dw - 1);

                if (dst_eff_dt.Month != dst_eff_mon)

                {

                    dst_eff_dt = dst_eff_dt.AddDays(-7);

                }

                DateTime dst_end_dt_test = new DateTime(origDate.Year, dst_end_mon, 1);

                dst_end_dt_test = dst_end_dt_test.AddDays((double)(-1 * DayOfWeekId(dst_end_dt_test.DayOfWeek)));

                DateTime dst_end_dt = dst_end_dt_test.AddDays(dst_end_wk * 7);

                dst_end_dt = dst_end_dt.AddHours(dst_end_hr);

                dst_end_dt = dst_end_dt.AddMinutes(dst_end_mi);

                dst_end_dt = dst_end_dt.AddDays(dst_end_dw - 1);

                if (dst_end_dt.Month != dst_end_mon)

                {

                    dst_end_dt = dst_end_dt.AddDays(-7);

                }

                DateTime newDate = origDate.AddHours(offsethr * -1).AddMinutes(offsetmi * -1);

                DateTime newDateDst = origDate.AddHours(dst_offsethr * -1).AddMinutes(dst_offsetmi * -1);

                bool isDST = false;

                if (origDate >= dst_eff_dt)

                {

                    if (origDate <= dst_end_dt)

                    {

                        isDST = true;

                    }

                }

                if (isDST)

                {

                    return (SqlDateTime)newDateDst;

                }

                else

                {

                    return (SqlDateTime)newDate;

                }

            }

            catch (Exception ex)

            {

                return OrigDate;

            }

        }

The next function is the function to convert UTC time to a specified timezone.  Similar to the previous function this one takes in two values, the date/time value and the time zone code needed to convert the date/time value to.

The logic of this function is nearly identical to the previous function so I will not go into great detail.  Like the previous function this one sets up a SQL statement, queries the database to get timezone parms, assigns those parms to variables, determins DST start and end dates relative to the provided date/time value, converts the time and determines whether to return the DST or standard time.  If any errors occur then the function will return the original date/time value.

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "CLR_TZ_TIME")]

public static SqlDateTime fnTzTime(SqlDateTime OrigDate, SqlString NewTimeZone)

{

    try

    {

        int offsethr = 0;

        int offsetmi = 0;

        int dst_offsethr = 0;

        int dst_offsetmi = 0;

        string dst_eff_key = "01110000";

        string dst_end_key = "01110000";

        string cmdSelectText = "SELECT " +

                    "offset_hr, " +

                    "offset_mi, " +

                    "dst_offset_hr, " +

                    "dst_offset_mi, " +

                    "dst_eff_dt, " +

                    "dst_end_dt " +

                "FROM time_zones " +

                "WHERE timezone_cd = '" + NewTimeZone.ToString() + "' AND " +

                    "CONVERT(DATETIME,'" + OrigDate.ToString() + "') BETWEEN eff_dt AND end_dt";

        using (SqlConnection conn = new SqlConnection("context connection=true"))

        {

            conn.Open();

            SqlCommand cmd = new SqlCommand(cmdSelectText, conn);

            SqlDataReader sdr = cmd.ExecuteReader();

            while (sdr.Read())

            {

                offsethr = int.Parse(sdr["offset_hr"].ToString());

                offsetmi = int.Parse(sdr["offset_mi"].ToString());

                dst_offsethr = int.Parse(sdr["dst_offset_hr"].ToString());

                dst_offsetmi = int.Parse(sdr["dst_offset_mi"].ToString());

                dst_eff_key = sdr["dst_eff_dt"].ToString();

                dst_end_key = sdr["dst_end_dt"].ToString();

            }

            conn.Close();

        }

        DateTime origDate = DateTime.Parse(OrigDate.ToString());

        int dst_eff_mon = int.Parse(dst_eff_key.Substring(0, 2));

        int dst_eff_wk = int.Parse(dst_eff_key.Substring(2, 1));

        int dst_eff_dw = int.Parse(dst_eff_key.Substring(3, 1));

        int dst_eff_hr = int.Parse(dst_eff_key.Substring(4, 2));

        int dst_eff_mi = int.Parse(dst_eff_key.Substring(6, 2));

        int dst_end_mon = int.Parse(dst_end_key.Substring(0, 2));

        int dst_end_wk = int.Parse(dst_end_key.Substring(2, 1));

        int dst_end_dw = int.Parse(dst_end_key.Substring(3, 1));

        int dst_end_hr = int.Parse(dst_end_key.Substring(4, 2));

        int dst_end_mi = int.Parse(dst_end_key.Substring(6, 2));

        DateTime dst_eff_dt_test = new DateTime(origDate.Year, dst_eff_mon, 1);

        dst_eff_dt_test = dst_eff_dt_test.AddDays((double)(-1 * DayOfWeekId(dst_eff_dt_test.DayOfWeek)));

        DateTime dst_eff_dt = dst_eff_dt_test.AddDays(dst_eff_wk * 7);

        dst_eff_dt = dst_eff_dt.AddHours(dst_eff_hr);

        dst_eff_dt = dst_eff_dt.AddMinutes(dst_eff_mi);

        dst_eff_dt = dst_eff_dt.AddDays(dst_eff_dw - 1);

        if (dst_eff_dt.Month != dst_eff_mon)

        {

            dst_eff_dt = dst_eff_dt.AddDays(-7);

        }

        DateTime dst_end_dt_test = new DateTime(origDate.Year, dst_end_mon, 1);

        dst_end_dt_test = dst_end_dt_test.AddDays((double)(-1 * DayOfWeekId(dst_end_dt_test.DayOfWeek)));

        DateTime dst_end_dt = dst_end_dt_test.AddDays(dst_end_wk * 7);

        dst_end_dt = dst_end_dt.AddHours(dst_end_hr);

        dst_end_dt = dst_end_dt.AddMinutes(dst_end_mi);

        dst_end_dt = dst_end_dt.AddDays(dst_end_dw - 1);

        if (dst_end_dt.Month != dst_end_mon)

        {

            dst_end_dt = dst_end_dt.AddDays(-7);

        }

        DateTime newDate = origDate.AddHours(offsethr).AddMinutes(offsetmi);

        DateTime newDateDst = origDate.AddHours(dst_offsethr).AddMinutes(dst_offsetmi);

        bool isDST = false;

        if (newDate >= dst_eff_dt)

        {

            if (newDate <= dst_end_dt)

            {

                isDST = true;

            }

        }

        if (isDST)

        {

            return (SqlDateTime)newDateDst;

        }

        else

        {

            return (SqlDateTime)newDate;

        }

    }

    catch (Exception ex)

    {

        return OrigDate;

    }

}

The last function for this process is the main function that will be used.  The logic for this function is pretty simple.  It utilizes the function calls to the previous two functions to make the necessary adjustments to the date/time value.

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "CLR_NEW_TIME")]

        public static SqlDateTime fnNewTime(SqlDateTime OrigDate, SqlString CurrentTimeZone, SqlString NewTimeZone)

        {

            try

            {

                SqlDateTime utcDate = fnUtcTime(OrigDate, CurrentTimeZone);

                SqlDateTime newDate = fnTzTime(utcDate, NewTimeZone);

                return newDate;

            }

            catch(Exception ex)

            {

                return OrigDate;

            }

        }

Now that the code has been written, compile it and then save a copy of the dll that is created to a location that you have access to (I used C:\Program Files (x86)\Microsoft SQL Server\).

You will need to open MS SQL SSMS (SQL Server Management Studio).  If you have not already created the database with the required time_zone table you will need to do that now (see previous post).

First you will need to set some database configurations so that SQL will can interact with the CLR.  This will not damage or prevent any risk to the database or the database server.  This is common to do though by default they are turned off.

Second, you will not need these set of commands unless you have already done this before.  It is good practice to have them even if you will not need them.  Drop the functions if they already exist.

Third, you will need to drop the assembly reference if one already exists and then register the current version.  One thing to note is that if you update the dll you cannot just replace it.  MS SQL does not reference the dll.  It actually copies it as binary to the database configuration so any time you update the dll you will need to go through this process again to update it.

Lastly you will need to run the commands to register the CLR functions as SQL functions on the database.  One thing to note is that a “string” datatype in the CLR function must have a matching NVARCHAR not VARCHAR passed value from MS SQL.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

-- =============================================

-- Drop existing instanced of objects

-- =============================================

if exists (select * from sys.objects o where o.name ='CLR_NEW_TIME')

BEGIN

    DROP FUNCTION CLR_NEW_TIME

END

GO

if exists (select * from sys.objects o where o.name ='CLR_TZ_TIME')

BEGIN

    DROP FUNCTION CLR_TZ_TIME

END

GO

if exists (select * from sys.objects o where o.name ='CLR_UTC_TIME')

BEGIN

    DROP FUNCTION CLR_UTC_TIME

END

GO

-- =============================================

-- Create ASSEMBLY reference to C# dll

-- =============================================

if exists (SELECT name FROM sys.assemblies o where o.name ='ExcendSolutions.SqlUtils')

BEGIN

    DROP ASSEMBLY [ExcendSolutions.SqlUtils]

END

GO

CREATE ASSEMBLY [ExcendSolutions.SqlUtils]

FROM 'C:\Program Files (x86)\Microsoft SQL Server\ExcendSolutions.SqlUtils.dll'

WITH PERMISSION_SET=EXTERNAL_ACCESS

GO

-- =============================================

-- Create scalar function references to C# CLR functions

-- =============================================

CREATE FUNCTION CLR_NEW_TIME(@ORIGDATE DATETIME,@CURRENTTIMEZONE NVARCHAR(6),@NEWTIMEZONE NVARCHAR(6))

RETURNS DATETIME

AS EXTERNAL NAME [ExcendSolutions.SqlUtils].SqlFunctions.fnNewTime

GO

CREATE FUNCTION CLR_TZ_TIME(@ORIGDATE DATETIME,@NEWTIMEZONE NVARCHAR(6))

RETURNS DATETIME

AS EXTERNAL NAME [ExcendSolutions.SqlUtils].SqlFunctions.fnTzTime

GO

CREATE FUNCTION CLR_UTC_TIME(@ORIGDATE DATETIME,@CURRENTTIMEZONE NVARCHAR(6))

RETURNS DATETIME

AS EXTERNAL NAME [ExcendSolutions.SqlUtils].SqlFunctions.fnUtcTime

GO

Assuming that you have no errors this is all you need and you are ready to rock and roll.  I was surprised at how easy it was to create and register a CLR function.  The only frustrating part of it was how hard it was to find good documentation on how do reference some of these elements properly.