<

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

Published on
9,113 Points
2,713 Views
4 Endorsements
Last Modified:
Approved
Kevin Cross
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
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:

Prerequisites.
Create DST utility table structure.
Iterative Approach.
Recursive Approach.


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

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
   )
 )

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

Open in new window


And to verify:
select dst_year, dst_to, dst_fr
from dst_utility

Open in new window


With the following results:
DST Example 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
;

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
http://www.experts-exchange.com/A_1639.html

Daylight Time
http://aa.usno.navy.mil/faq/docs/daylight_time.php
4
Comment
Author:Kevin Cross
0 Comments

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month