Creating the Timezone function as a C# CLR function with hard coded parms

In my effort to find the most efficient method for creating a timezone conversion function in MS SQL I have tried a pure T-SQL solution, CLR function that queries SQL tables, a CLR function that uses an XML file and this article will show a CLR function where the timezone parms are hard coded in the dll.

I approached this CLR function in the same manner as the other processes.  I have the administrative class to manage the timezone parms for the functions.

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; }

    }

}

One of the divergences with this process from the other processes is that I have another class that contains all the parm information in it.  One of the bad elements of this process is that anytime you want to add or change the parms you would have to recompile and reregister the dll and functions on the SQL server.  The class definition for the parm data is below.

public static class TimeZoneData

{

    public static List<TimeZoneParm> GetList()

    {

        List<TimeZoneParm> lsTZP = new List<TimeZoneParm>();

        TimeZoneParm tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "AT";

        tzp.TimeZoneName = "Atlantic Time";

        tzp.OffSetHr =-4;

        tzp.OffSetMi = 0;

        tzp.DstOffSetHr = -3;

        tzp.DstOffSetMi = 0;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "AZ";

        tzp.TimeZoneName = "Arizona Time";

        tzp.OffSetHr =-7;

        tzp.OffSetMi = 0;

        tzp.DstOffSetHr = -7;

        tzp.DstOffSetMi = 0;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "CT";

        tzp.TimeZoneName = "Central Time";

        tzp.OffSetHr =-6;

        tzp.OffSetMi = 0;

        tzp.DstOffSetHr = -5;

        tzp.DstOffSetMi = 0;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "ET";

        tzp.TimeZoneName = "Eastern Time";

        tzp.OffSetHr =-5;

        tzp.OffSetMi = 0;

        tzp.DstOffSetHr = -4;

        tzp.DstOffSetMi = 0;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "HT";

        tzp.TimeZoneName = "Hawaii Time";

        tzp.OffSetHr =-10;

        tzp.OffSetMi = 0;

        tzp.DstOffSetHr = -10;

        tzp.DstOffSetMi = 0;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "MT";

        tzp.TimeZoneName = "Mountain Time";

        tzp.OffSetHr =-7;

        tzp.OffSetMi = 0;

        tzp.DstOffSetHr = -6;

        tzp.DstOffSetMi = 0;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "NT";

        tzp.TimeZoneName = "Newfoundland Time";

        tzp.OffSetHr =-4;

        tzp.OffSetMi = 30;

        tzp.DstOffSetHr = -2;

        tzp.DstOffSetMi = 30;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "PT";

        tzp.TimeZoneName = "Pacific Time";

        tzp.OffSetHr =-8;

        tzp.OffSetMi = 0;

        tzp.DstOffSetHr = -7;

        tzp.DstOffSetMi = 0;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        tzp = new TimeZoneParm();

        tzp.TimeZoneCd = "YT";

        tzp.TimeZoneName = "Yukon Time";

        tzp.OffSetHr =-8;

        tzp.OffSetMi = 0;

        tzp.DstOffSetHr = -8;

        tzp.DstOffSetMi = 0;

        tzp.DstEffDt = "03210200";

        tzp.DstEndDt = "11110100";

        lsTZP.Add(tzp);

        return lsTZP;

    }

    public static TimeZoneParm GetTimeZoneParm(string TimeZoneCode)

    {

        List<TimeZoneParm> lsTZP = GetList();

        TimeZoneParm tzp = new TimeZoneParm();

        foreach(TimeZoneParm t in lsTZP)

        {

            if (t.TimeZoneCd == TimeZoneCode)

            {

                tzp = t;

                break;

            }

        }

        return tzp;

    }

}

The next divergence in this process is that since all of the information is compiled there is no need to query a table or file for the timezone parm info.  You just need to make the api function call.  The logic for the function that converts a date/time value to UTC time is the same as the other processes.  It gets the parms, determines DST start and end dates, converts the provided date/time value to DST and standard for the provided timezone code and then determines whether to use the DST or standard date/time value.

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

public static SqlDateTime fnApiUtcTime(SqlDateTime OrigDate, SqlString CurrentTimeZone)

{

    try

    {

        TimeZoneParm tzp = TimeZoneData.GetTimeZoneParm(CurrentTimeZone.ToString());

        int offsethr = 0;

        int offsetmi = 0;

        int dst_offsethr = 0;

        int dst_offsetmi = 0;

        string dst_eff_key = "01110000";

        string dst_end_key = "01110000";

        offsethr = tzp.OffSetHr;

        offsetmi = tzp.OffSetMi;

        dst_offsethr = tzp.DstOffSetHr;

        dst_offsetmi = tzp.DstOffSetMi;

        dst_eff_key = tzp.DstEffDt;

        dst_end_key = tzp.DstEndDt;

        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 one that converts a date/time value from UTC to a provided timezone code.  This is similar to the previous function so I will not go into all the details of the function.

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

public static SqlDateTime fnApiTzTime(SqlDateTime OrigDate, SqlString NewTimeZone)

{

    try

    {

        TimeZoneParm tzp = TimeZoneData.GetTimeZoneParm(NewTimeZone.ToString());

        int offsethr = 0;

        int offsetmi = 0;

        int dst_offsethr = 0;

        int dst_offsetmi = 0;

        string dst_eff_key = "01110000";

        string dst_end_key = "01110000";

        offsethr = tzp.OffSetHr;

        offsetmi = tzp.OffSetMi;

        dst_offsethr = tzp.DstOffSetHr;

        dst_offsetmi = tzp.DstOffSetMi;

        dst_eff_key = tzp.DstEffDt;

        dst_end_key = tzp.DstEndDt;

        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 is the main function that will be used to convert a date/time value from a provided timezone code to a provided timezone code.  The process for this function is pretty simple.  It uses the previous two functions to convert the date/time value to UTC from a provided timezone code and then uses the function to convert from UTC to the provided timezone code.

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

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

{

    try

    {

        SqlDateTime utcDate = fnApiUtcTime(OrigDate, CurrentTimeZone);

        SqlDateTime newDate = fnApiTzTime(utcDate, NewTimeZone);

        return newDate;

    }

    catch (Exception ex)

    {

        return OrigDate;

    }

}

The next post will include performance results for all the processes to see which process is most efficient.