Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
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...
What you need; what you get.
Validate input and default values just in case.
It's all in the date.
The User Defined Function Code.
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 datetimeas begin -- local variables / validated input declare @result datetime , @yr int , @mo tinyint , @dw tinyint ; -- processing ; -- output return @result ;end
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 )
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)
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)
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)
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 datetimeas 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
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.
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:
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 datetimeas 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
-- Change language to French if in English system as I am;-- users already in French system, remove both set lines.set language French ;-- Samedi == Saturdayselect dbo.fn_OccurrenceOfDaynameInMonth( 2009, 2, 'Samedi', 1, 1 ) ;set language English ;
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
By the way, this is a good tool when particular date (occurrence) is needed, but what also comes up is needing to plug gaps so basically get all the dates in a specific range.
For that you need a table of dates or numbers that you can associate to dates :
I showed one technique using recursive CTE above as part of the date occurrence solution, but here are some other articles that are good reads on this topic :
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Comments (5)
Commented:
Author
Commented:Comes in handy every once in a while.
Author
Commented:For that you need a table of dates or numbers that you can associate to dates :
I showed one technique using recursive CTE above as part of the date occurrence solution, but here are some other articles that are good reads on this topic :
Delimited String Parsing in SQL Server 2005 and later
https://www.experts-exchange.com/A_192.html (see vw_nums)
Fun with MS SQL spt_values for delimited strings and virtual calendars
https://www.experts-exchange.com/A_1221.html
Commented:
soon it may come in handy for me..!
Author
Commented:Thank you for the vote also as well as others I had not yet acknowledged. I am just happy to have helped in some way.
Best regards,
Kevin