Going UTC - Working with Daylight savings in SQL (BST/GMT)

Early in my IT carrer, all servers and data was calculated and stored in UK Time with Daylight Savings.  As my IT experiance advanced, I increasingly needed to work with multi-timezone data and/or systems being access from differant timezones.

As a SQL Server DBA, it became increasinly important to know what time an event happend.  All my server estate was configured to UK with Daylight Savings time.  Every year I have 1 hour of missing from March and an hour repeated in October.  Something needed to change.

I started encuraging the use of UTC time and DateTime-Offset data types, but these are not Daylight Savings aware and systems needed to dislay and calculate based on UK Time.

So, I created a collection of SQL CLR functions that could be used to calculate and convert to and from UK Time with Daylight savings to UTC time.

The rules for Daylight Savings time are as follows:

In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.

The period when the clocks are 1 hour ahead is called British Summer Time (BST). There’s more daylight in the evenings and less in the mornings (sometimes called Daylight Saving Time).

When the clocks go back, the UK is on Greenwich Mean Time (GMT).

The code:

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = true, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlDateTime BSTStart(SqlInt32 year)

    {

        DateTime dt = new DateTime(year.Value, 3, 31);

        while (dt.DayOfWeek != DayOfWeek.Sunday)

        {

            dt = dt.AddDays(-1);

        }

        return new SqlDateTime(dt.AddHours(1));

    }

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = true, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlDateTime BSTEnd(SqlInt32 year)

    {

        DateTime dt = new DateTime(year.Value, 10, 31);

        while (dt.DayOfWeek != DayOfWeek.Sunday)

        {

            dt = dt.AddDays(-1);

        }

        return new SqlDateTime(dt.AddHours(2));

    }

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = true, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlBoolean IsBST(SqlDateTime dt)

    {

        return (dt >= BSTStart(dt.Value.Year) && dt <= BSTEnd(dt.Value.Year));

    }

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = false, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlDateTime GETUKTIME()

    {

        DateTime dt = DateTime.UtcNow;

        if (dt < BSTStart(dt.Year) || dt > BSTEnd(dt.Year)) return new SqlDateTime(dt);

        return new SqlDateTime(dt.AddHours(1));

    }

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = true, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlDateTime ToUKTime(SqlDateTime dt)

    {

        if (dt < BSTStart(dt.Value.Year) || dt > BSTEnd(dt.Value.Year)) return new SqlDateTime(dt.Value);

        return new SqlDateTime(dt.Value.AddHours(1));

    }