Date of the n-th Day in a Month

Ever wondered the date a "day" first occurred?

Realize I am not talking about the beginning of time?

Use SQL Server 2005?

Then this article is for you!

In this SQL Server 2005 tip you will see how to determine the date on which a requested day of the week occurred in any month of any year.  A typical scenario would be in a calendaring system that allows you to establish meetings on the first Monday of every month.  Consequently, as this function has evolved over time, its ability now goes beyond just the first occurrence and can return the second, third, ..., or even last occurrence of a given weekday.

Since a month can start on any day of the week, having an ordinal weekday calculation is not a trivial task.  For example: the first Monday in August 2009 fell on the 3rd of August; the first Tuesday for September 2009, September 1st; the last Tuesday of September 2008, September 30th.

With that in mind, let's begin going through the sections...

1. What you need; what you get.

Given a year and month, our function will get a specific weekday occurrence using its integer day of week value (each locale may use a different @@datefirst value, but, typically for the US, Sunday is 1; Monday, 2; etc.) and numbered instance representing 1st, 2nd, 3rd, and so on.  

To make it easy to find the last occurrence, which is difficult when number of times a day appears in a month is unknown, we have a secondary ranking that orders in reverse; therefore, optionally, we can set a flag (e.g., @lastFirst) that indicates the last occurrence should be treated as row #1.  With this capability, we can simply search for @occurrence = 1 with @lastFirst = 1.  The 2nd to last day would then be @occurrence = 2 and so on...

After processing these inputs, a datetime value will be returned that represent the date on which this n-th day in the month occurs by matching the criteria above.

Here is a look at how that translates to the function's signature and skeleton structure:

create function dbo.fn_OccurrenceOfWeekdayInMonth(
                         -- input
                         @year int
                       , @month tinyint
                       , @weekday tinyint
                       , @occurrence tinyint
                       , @lastFirst bit
                      )
                      returns datetime
                      as 
                      begin
                         -- local variables / validated input
                         declare @result datetime
                               , @yr int
                               , @mo tinyint
                               , @dw tinyint
                         ;
                      
                         -- processing
                         ; 
                        
                         -- output
                         return @result ;
                      end
                      

Select all Open in new window

Are you sure you know what you need?

2. Validate input and default values just in case.

In SQL Server, including version 2005, the datetime data type is limited to years 1753 to 9999.
(DATE and TIME ... don't be scared, and do it right (the first time))

if (coalesce(@year, 0) not between 1753 and 9999) set @yr = year( getdate() )
                      else set @yr = @year
                      

Select all Open in new window

Only 12 months in a year:

if (coalesce(@month, 0) not between 1 and 12) set @mo = month( getdate() )
                      else set @mo =  @month
                      

Select all Open in new window

Only 7 days in a week:

if (coalesce(@weekday, 0) not between 1 and 7) set @dw = 1
                      else set @dw = @weekday
                      

Select all Open in new window

For this implementation, I decided to return null for invalid occurrence values.  Rationale is that the above verification is all that is required to establish valid initialization.  If I ask for the 10th Monday in August, then null is pretty reasonable response.  Disagree?  No worries, modify this once you understand the base functionality.

Moving on...

3. It's all in the date.

Why re-invent the wheel?  The datetime object keeps track of it all for us: the day of the week, the week, the month, the year; therefore, the trick is to construct a virtual table of all the dates in the month (and, if your adventurous, any given duration of time).  Once we have our table of dates, we can take advantage of the natural ordering and date functions of the datetime type.

To construct our virtual table, we take advantage of common table expressions and their recursive capabilities:
(Not going to go into too much detail here on how recursion works in CTE; therefore, I would recommend you read this article by EE's own Aaron Akin)

with dates( dt )
                      as
                      (
                         -- select 1st day of month constructed from year and month inputs
                         select convert( datetime, 
                                  convert( char(8), 
                                     @yr * 10000 + @mo * 100 + 1 
                                  ) 
                               ) 
                      
                         union all -- facilitates recursion
                      
                         -- add in remainder of days in month
                         select dt + 1
                         from dates
                         -- keeps adding until the next day would be 1st of next month
                         where day( dt + 1 ) <> 1 
                      )
                      

Select all Open in new window

So, we just create a base record with the first day of the month and then used union to join that to a selection of this first record +1.  Moreover, CTEs are virtual tables so our new second row is subject to same select  ... from dates statement and so as long as adding a day via +1 doesn't violate the where clause filtering, which checks that the day of the month of the new date is not 1 (meaning 1st of next month), we will continue to get one new one recursively until our table is filled with all the dates in the month.  

Try it, by simply adding this select immediately after the CTE definition above:
(note you will have to have @yr and @mo variables from above declared and set appropriately)

select dt from dates ;
                      

Select all Open in new window

For September 2009, you should see these results :

However, for our usage, we need more substance than this, but instead of trying to do the date plus calculation at the same time we do other calculations, I opted to keep the recursion separate and utilize the "nesting" capabilities of CTE which I again feel Aaron has done a great job explaining.

So adding our second CTE definition would look like this:

, dates_tagged( dt, dw, occurrence, occurrence_reverse )
                      as
                      (
                         select dt, datepart( weekday, dt )
                              , row_number() 
                                   over( partition by datepart( weekday, dt ) 
                                         order by dt )
                              , row_number() 
                                   over( partition by datepart( weekday, dt ) 
                                         order by dt desc )
                         from dates
                      )
                      

Select all Open in new window

Note the comma (,) replaced the WITH keyword and joins to the two CTE statements together (so if you were wondering, yes, the "with dates ..." is still in the code).  Additionally, we are also adding our value-add information like the day of the week for each date along with its ordinal ranking with respect to other dates for same day of the week.  Using the row_number() function with both ascending and descending order by dt (date) is the key to our aforementioned ability to search the last occurrence as easily as the first.  This way @occurrence = 1 coupled with value of @lastFirst gives us the correct of the two occurrences.

For more detailed information on row_number() and other analytical ranking functions within SQL Server 2005, please refer to my EE article in reference.

Think that about does it, but if you are curious then try it similar to what we did above:
(as mentioned, remember to declare and set variables as well as dates CTE)

select dt, dw, occurrence, occurrence_reverse
                      from dates_tagged
                      ; 
                      

Select all Open in new window

Or, for your convenience, you can download and execute this T-SQL script :
datesTaggedCTE.sql.txt
And the new results for September 2009 :

Now, putting it all together...

4. The User Defined Function Code.

We add processing to the function by first creating a virtual table of all the dates in the requested month and then utilizing analytical functions determine the occurrence number for each weekday.

alter function dbo.fn_OccurrenceOfWeekdayInMonth(
                         @year int
                       , @month tinyint
                       , @weekday tinyint
                       , @occurrence tinyint
                       , @lastFirst bit
                      )
                      returns datetime
                      as 
                      begin
                         -- local variables
                         declare @result datetime
                               , @yr int
                               , @mo tinyint
                               , @dw tinyint
                         ; -- handle invalid year value
                         if (coalesce(@year, 0) not between 1753 and 9999) set @yr = year( getdate() )
                         else set @yr = @year
                         ; -- handle invalid month value
                         if (coalesce(@month, 0) not between 1 and 12) set @mo = month( getdate() )
                         else set @mo =  @month
                         ; -- handle invalid day of week value
                         if (coalesce(@weekday, 0) not between 1 and 7) set @dw = 1
                         else set @dw = @weekday
                         ; -- construct date time common table expression to make things easier
                         with dates( dt )
                         as
                         (
                            -- select 1st day of month constructed from year and month inputs
                            select convert( datetime, 
                                     convert( char(8), 
                                        @yr * 10000 + @mo * 100 + 1 
                                     ) 
                                  ) 
                      
                            union all -- facilitates recursion
                      
                            -- add in remainder of days in month
                            select dt + 1
                            from dates
                            -- keeps adding until the next day would be 1st of next month
                            where day( dt + 1 ) <> 1 
                         )
                         -- utilize the cte of dates and tag each with occurrence value,
                         -- using ranking function(s)
                         , dates_tagged( dt, dw, occurrence, occurrence_reverse )
                         as
                         (
                            select dt, datepart( weekday, dt )
                                 , row_number() 
                                      over( partition by datepart( weekday, dt ) 
                                            order by dt )
                                 , row_number() 
                                      over( partition by datepart( weekday, dt ) 
                                            order by dt desc )
                            from dates
                         )
                         select @result = dt
                         from dates_tagged
                         where dw = @dw
                           and ((@lastFirst = 1 and occurrence_reverse = @occurrence)
                            or (coalesce(@lastFirst, 0) = 0 and occurrence = @occurrence))
                         ; -- return result to caller
                         return @result ;
                      end
                      

Select all Open in new window

The code should speak for itself and be relatively straight forward to follow; however, please leave comments below if you want further clarification or explanations.

Example usage:

select dbo.fn_OccurrenceOfDaynameInMonth( 2009, 9, 1, 1, 0 ) ;
                      

Select all Open in new window

So, the first time the first day of the week (e.g., Sunday for me) occurs in September 2009 is:

2009-09-06 00:00:00.000
                      

Select all Open in new window

...And that's it!

We now have another function to add to our utilities database / tool-belt that allows us to find a specific occurrence of a certain day of the week in a given month.  With this being simple, the value here is its flexibility to be used regardless of your local settings for which day of the week is which 1 - 7 value.  Consequently, as a bonus for reading, you will find in the appendix a make-shift function overload allowing you to call this utility with a day name in your given locale.

Thanks for reading!

For some more fun and a real-world application of this article's function, please read this article:

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

Happy coding!

Best regards,

Kevin (aka MWVisa1)


Appendix A: A more human-friendly invocation.

Here is a wrapper function to allow us to call our weekday code by day name (e.g., Tuesday (English) or Martes (Spanish) or Dienstag (German) -- language doesn't matter) instead of the day number.

create function dbo.fn_OccurrenceOfDaynameInMonth(
                         @year int
                       , @month tinyint
                       , @dayname varchar(15)
                       , @occurrence tinyint
                       , @lastFirst bit
                      )
                      returns datetime
                      as 
                      begin
                         -- local variables
                         declare @result datetime
                               , @weekday tinyint
                         declare @daynames table( 
                                    dayname varchar(15) primary key
                                  , weekday tinyint 
                                 )
                         ; -- translate day name to day of week number
                         set @weekday = 1 -- use week day variable temporarily as counter
                         while (@weekday <= 7)
                         begin
                            -- use result variable temporarily to store dates
                            set @result = getdate() - @weekday
                            insert into @daynames 
                            values( datename( weekday, @result )
                                  , datepart( weekday, @result ) 
                            )
                            set @weekday = @weekday + 1 -- increment counter
                         end
                         select @weekday = weekday 
                         from @daynames 
                         where dayname = @dayname
                         ; -- utilize dbo.fn_OccurrenceOfWeekdayInMonth to get result
                         set @result = 
                            dbo.fn_OccurrenceOfWeekdayInMonth(
                               @year, @month, @weekday, @occurrence, @lastFirst
                            )
                         ; 
                         return @result ;
                      end
                      

Select all Open in new window

Example usage:

select dbo.fn_OccurrenceOfDaynameInMonth( 2008, 2, 'Thursday', 1, 1 ) ;
                      

Select all Open in new window

So, the last (notice 1 for the @lastFirst parameter) Thursday in February 2008 is:

2008-02-28 00:00:00.000
                      

Select all Open in new window

(alternate language example)

-- Change language to French if in English system as I am;
                      -- users already in French system, remove both set lines.
                      set language French ;
                      -- Samedi == Saturday
                      select dbo.fn_OccurrenceOfDaynameInMonth( 2009, 2, 'Samedi', 1, 1 ) ;
                      set language English ;
                      

Select all Open in new window

Le dernier samedi de Février 2009 est:
(last Saturday in February 2009)

2009-02-28 00:00:00.000
                      

Select all Open in new window

Challenge:
Can you guess the dates for the following?

Utilize our new code to check your answers to the above.  

Have fun!


Related Resources / References:

Analytical SQL : Where do you rank?
https://www.experts-exchange.com/A_1555.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 and TIME ... don't be scared, and do it right (the first time)
https://www.experts-exchange.com/A_1499.html

DATEFIRST
http://msdn.microsoft.com/en-us/library/ms187766(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms181598(SQL.90).aspx

DST Utilities Table Creation : Using occurrence of a weekday practically!
https://www.experts-exchange.com/A_1640.html