DST Utilities Table Creation : Using occurrence of a weekday practically!

Daylight savings time utilities table creation, as the title suggests, is the goal of this article and utilizes our learning from my previous article "Date of the n-th Day in a Month."

Well, given we can create this table by manually entering dates, this article is more precisely aimed at showing how to dynamically generate a table that can be used as a utility in order to lookup / match date records to being either within DST window or not in MS SQL Server queries.

Article Sections:

1. Prerequisites.

As stated earlier, we are building on my previous article, which you should read for explanation; however, please run the attached SQL code to create the required T-SQL user defined functions dbo.fn_OccurrenceOfWeekdayInMonth and dbo.fn_OccurrenceOfDaynameInMonth.
OccurrenceOfWeekday.sql.txt

Let's begin...

2. Create DST utility table structure.

Simplified DDL.  Modify to your needs.

create table dst_utility(
                         dst_yr int primary key, 
                         dst_to datetime, 
                         dst_fr datetime
                      )
                      ;

Select all Open in new window

Okay, let's move on...

3. Iterative Approach.

Reminder: the examples shown are for the US and presume knowledge of daylight savings time; therefore, if you are in a different country or would like additional information, please read "About Daylight Savings Time" reference.

In this multi-line T-SQL statement approach we utilize a while loop by year and variables for the month, occurrence (and last flag) to dynamically adjust the DST rules as we traverse years in the table.

Once these values are established, we can just utilize our user defined function from the previous article:

dateadd(hour, 2
                          , dbo.fn_OccurrenceOfDaynameInMonth(
                               @year
                             , @monthTo
                             , 'Sunday'
                             , @startOccurrence
                             , @startLastFirst
                         )
                       )

Select all Open in new window

Again to demonstrate, flexibility to move between countries or varying system settings note that using 'Sunday' instead of a hard coded 1.  For other languages, if the proper day name for 'Sunday' is used instead the results should not be altered making this portable.

With that said, here is the full code for this approach:

declare @monthTo tinyint
                            , @startOccurrence tinyint
                            , @startLastFirst tinyint
                            , @monthFr tinyint
                            , @endOccurrence tinyint
                            , @endLastFirst tinyint
                            , @year int
                      ;
                      set @year = 2000
                      while (@year < 2100)
                      begin
                         -- handle change in DST laws / rules
                         if (@year < 2007)
                         begin
                            -- old dst for US / adjust by country
                            -- Start: First Sunday in April
                            set @monthTo = 4
                            set @startOccurrence = 1
                            set @startLastFirst = 0
                            -- End: Last Sunday in October
                            set @monthFr = 10
                            set @endOccurrence = 1
                            set @endLastFirst = 1
                         end
                         else
                         begin
                            -- new dst for US / adjust by country
                            -- Start: Second Sunday in March
                            set @monthTo = 3
                            set @startOccurrence = 2
                            set @startLastFirst = 0
                            -- End: First Sunday in November
                            set @monthFr = 11
                            set @endOccurrence = 1
                            set @endLastFirst = 0
                         end
                         -- insert new dst record
                         insert into dst_utility
                         select @year
                              , dateadd(hour, 2
                                   , dbo.fn_OccurrenceOfDaynameInMonth(
                                        @year
                                      , @monthTo
                                      , 'Sunday'
                                      , @startOccurrence
                                      , @startLastFirst
                                   )
                                )
                              , dateadd(hour, 2
                                   , dbo.fn_OccurrenceOfDaynameInMonth(
                                        @year
                                      , @monthFr
                                      , 'Sunday'
                                      , @endOccurrence
                                      , @endLastFirst
                                   )
                                )
                         -- increment year
                         set @year = @year + 1
                      end

Select all Open in new window

And to verify:

select dst_year, dst_to, dst_fr
                      from dst_utility

Select all Open in new window

With the following results:

...some more fun with CTEs?

4. Recursive Approach. (SQL 2005 or higher)

As we did with our dates in our user defined function, we can utilize common table expression recursion capabilities to allow us to do this in one T-SQL statement without variables.  This statement is great because, we can do much more with this like creating a view.  Try it for yourself: add create view as syntax; remove the insert line; and name the two columns in the final select.

Have fun:

with years( yr )
                      as
                      (
                         -- set base year
                         select 2000
                      
                         union all -- cte recursion
                      
                         -- add additional years
                         select yr + 1
                         from years
                         where yr + 1 < 2100
                      )
                      , dst_rules( yr
                           , monthTo, weekdayTo, occurrenceTo, lastFirstTo
                           , monthFr, weekdayFr, occurrenceFr, lastFirstFr
                        )
                      as
                      (
                         select yr
                              , case when yr < 2007 then 4 else 3 end, 1
                              , case when yr < 2007 then 1 else 2 end, 0
                              , case when yr < 2007 then 10 else 11 end, 1
                              , 1, case when yr < 2007 then 1 else 0 end
                         from years
                      )
                      -- insert new dst record
                      insert into dst_utility
                      select yr
                           , dateadd(hour, 2
                                , dbo.fn_OccurrenceOfDaynameInMonth(
                                     yr
                                   , monthTo
                                   , weekdayTo
                                   , occurrenceTo
                                   , lastFirstTo
                                )
                             )
                            , dateadd(hour, 2
                                , dbo.fn_OccurrenceOfDaynameInMonth(
                                     yr
                                   , monthFr
                                   , weekdayFr
                                   , occurrenceFr
                                   , lastFirstFr
                                )
                             )
                      from dst_rules
                      ;

Select all Open in new window

Presto, we did it!

With a little help from With() (pun intended), so please remember to read Aaron's articles on the subject and refer to Microsoft's books online for further details on how nesting and recursion works within common table expressions as I believe he does a good job.  CTE approaches are very handy and a strong addition in its own right to our mental toolkits.

Another journey complete...and hopefully enjoyed!  This was just one example of how to utilize our occurrence of a weekday function(s) and can be pretty useful as is a numbers table and other such utilities discussed in other articles here on EE; therefore, use it wisely and learn from it all you can.

Happy coding!

Best regards,

Kevin (aka MWVisa1)


Downloads:
createDSTtable.sql.txt
createDSTtable2.sql.txt

Related Resources / References:

About Daylight Savings Time
http://www.timeanddate.com/time/aboutdst.html

Common Table Expressions (WITH() clause)
http://msdn.microsoft.com/en-us/library/ms190766.aspx 
(nice reference on nested and recursive CTEs by Aaron Akin)
http://sqlservernation.com/blogs/development/archive/2009/03/18/nested-common-table-expressions.aspx
http://aaronakinsql.wordpress.com/2009/01/20/recursive-common-table-expressions-part-1/

Date of the n-th Day in a Month
https://www.experts-exchange.com/A_1639.html

Daylight Time
http://aa.usno.navy.mil/faq/docs/daylight_time.php