Use SQL to convert UTC time to local time

Introduction

We have a requirement that need implement a SQL function which can convert UTC time to any timezone's localtime. Not just current time, but need also support historical date. I find Chris's solution and make some code changes to meet our need.

Use

Code can be downloaded from here

To setup and test the timezone conversion function:

1. Download and unzip sql_timezone_conversion_function.zip file.
2. open SSMS(Sql server management studio), login to Sql server, open a query window to database [A].
3. Run queries in “setup sql.sql” to create function and related table in target database [A].
4. Open .NET project in SQLServerTimeZoneUtility.zip file, build and run, input sql connection string which connects database [A], it will export Windows system timezone rules to SqlAzure tables.
5. To make a simple test, you can run “test sql.sql” in SSMS query window.

In future, to update timezone rules, just run SQLServerTimeZoneUtility tool again.

Design

Each Timezone may have 0..n daylight rules, each rule covers a time range. My design is having two tables, one store timezone base offset, the other store daylight rules

When input a utc date, the function would query tbTimeZoneRules to determine which rule fit the input date. Conversion function's sequence is as below

Since the timezone rules may change in future, we need also create an application which can export Windows system's timezone rules and update the rules in tbTimeZoneRules. Code is as below:

private static void UpdateTimeZoneRules(string connstr)
        {
            try
            {
                using (SqlConnection sqlconn = new SqlConnection(connstr))
                {
                    sqlconn.Open();

                    // clear table
                    SqlCommand sqlcmd0 = new SqlCommand(
                        "delete from tbTimeZonerules;delete from tbTimeZoneinfo",
                        sqlconn);
                    sqlcmd0.ExecuteNonQuery();

                    // read windows os timezone infos
                    ReadOnlyCollection timeZones = TimeZoneInfo.GetSystemTimeZones();
                    string[] monthNames = CultureInfo.CurrentCulture.DateTimeFormat.MonthNames;

                    // Get each time zone
                    foreach (TimeZoneInfo timeZone in timeZones)
                    {
                        string tzname = timeZone.StandardName;
                        double bias = timeZone.BaseUtcOffset.TotalMinutes;

                        // insert tbTimeZoneInfo table
                        SqlCommand sqlcmd = new SqlCommand(
                            string.Format("insert into tbTimeZoneInfo(Display,Bias) values('{0}',{1});select @@IDENTITY;", tzname, bias),
                            sqlconn);
                        decimal id = (decimal)sqlcmd.ExecuteScalar();

                        // insert TbTimeZoneRules table
                        TimeZoneInfo.AdjustmentRule[] adjustments = timeZone.GetAdjustmentRules();
                        foreach (var rule in adjustments)
                        {
                            DateTime datestart = rule.DateStart;
                            if (datestart < new DateTime(1753, 1, 2))
                                datestart = new DateTime(1753, 1, 2);

                            DateTime dateend = rule.DateEnd.AddSeconds(-1).AddDays(1);

                            SqlCommand sqlcmd2 = new SqlCommand(
                                "insert into tbTimeZoneRules(rulestart,ruleend,TimeZoneID,StdBias,DltBias,StdMonth,StdDayofWeek,StdWeek,StdHour,DltMonth,DltDayOfWeek,DltWeek,Dlthour,stdMinute,dltminute) values(@rulestart,@ruleend,@tzid,@stdbias,@dltbias,@stdmonth,@stddw,@stdw,@stdh,@dltmonth,@dltdw,@dltw,@dlth,@stdm,@dltm)",
                                sqlconn);
                            sqlcmd2.Parameters.Add("@rulestart", SqlDbType.DateTime).Value = datestart;
                            sqlcmd2.Parameters.Add("@ruleend", SqlDbType.DateTime).Value = dateend;
                            sqlcmd2.Parameters.Add("@tzid", SqlDbType.Int).Value = id;
                            sqlcmd2.Parameters.Add("@stdbias", SqlDbType.SmallInt).Value = 0;
                            sqlcmd2.Parameters.Add("@dltbias", SqlDbType.SmallInt).Value = rule.DaylightDelta.TotalMinutes;
                            sqlcmd2.Parameters.Add("@stdmonth", SqlDbType.SmallInt).Value = rule.DaylightTransitionEnd.Month;
                            sqlcmd2.Parameters.Add("@stddw", SqlDbType.SmallInt).Value = (int)rule.DaylightTransitionEnd.DayOfWeek;
                            sqlcmd2.Parameters.Add("@stdw", SqlDbType.SmallInt).Value = rule.DaylightTransitionEnd.Week;
                            sqlcmd2.Parameters.Add("@stdh", SqlDbType.SmallInt).Value = rule.DaylightTransitionEnd.TimeOfDay.Hour;
                            sqlcmd2.Parameters.Add("@stdm", SqlDbType.SmallInt).Value = rule.DaylightTransitionEnd.TimeOfDay.Minute;
                            sqlcmd2.Parameters.Add("@dltmonth", SqlDbType.SmallInt).Value = rule.DaylightTransitionStart.Month;
                            sqlcmd2.Parameters.Add("@dltdw", SqlDbType.SmallInt).Value = (int)rule.DaylightTransitionStart.DayOfWeek;
                            sqlcmd2.Parameters.Add("@dltw", SqlDbType.SmallInt).Value = rule.DaylightTransitionStart.Week;
                            sqlcmd2.Parameters.Add("@dlth", SqlDbType.SmallInt).Value = rule.DaylightTransitionStart.TimeOfDay.Hour;
                            sqlcmd2.Parameters.Add("@dltm", SqlDbType.SmallInt).Value = rule.DaylightTransitionStart.TimeOfDay.Minute;
                            sqlcmd2.ExecuteNonQuery();
                        }
                        Console.WriteLine("{0} imported.", timeZone.DisplayName);
                    }
                }
                Console.WriteLine("Timezone rules update succeed.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Timezone rules update failed.\n" + ex.Message);
            }
        }