Finding the nth Weekday of a given month

[Edited from original – my colleague pointed out that the method I was using for getting the weekday returned different results depending on the DATEFIRST setting – the new solution is actually cleaner!]

A colleague of mine asked me for a utility to find the nth weekday of the month given a date, a number and a word such as “Wednesday”. He further challenged me to see if I could do it in one statement.

So for example, a call such as this:

SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Wednesday',3)

When run on any day in July, 2011, will return the following:

-----------------------
2011-07-20 00:00:00.000

(1 row(s) affected)

Which is the 3rd Wednesday of this month.  The date parameter serves only to identify the month (and year), so any day or time information can be ignored.  It is a fairly straightforward problem, and when thought of iteratively, straightforward solutions start coming to mind – but iterative thinking is not going to solve this in a single statement.

From a set-based perspective, you think of the set of dates that make up the month in question, then simply choosing the right one – so the first item of business is to get a set of all the dates for the month in question.  As with many solutions, this will require a numbers or tally table (simply a table with nothing but numbers in it).  Many people have such a table at their disposal, but if you don’t (and you don’t need very many numbers – here we’ll only need a maximum of 31) you can use master..spt_values as discussed in a previous post.  So starting with the first day of the month, if we add the numbers from our tally table in days, we end up with a list of days of the month (we’re clearly violating the one-statement rule, but stay tuned – we’ll bring it all together):

DECLARE @dt DATETIME = GETDATE();
DECLARE @dow VARCHAR(100) = 'Wednesday';
DECLARE @num INT = 3;

--This will take off any time portion and set the value to the beginning of the month
SET @dt = DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0);

SELECT DATEADD(DAY,number-1,@dt) AS MonthDate
FROM MASTER..spt_values
WHERE number > 0
    AND TYPE = 'P'
    AND DATEADD(DAY,number-1,@dt) >= @dt
    AND DATEADD(DAY,number-1,@dt) < DATEADD(MONTH,1,@dt)

MonthDate
-----------------------
2011-07-01 00:00:00.000
2011-07-02 00:00:00.000
2011-07-03 00:00:00.000
2011-07-04 00:00:00.000
2011-07-05 00:00:00.000
2011-07-06 00:00:00.000
2011-07-07 00:00:00.000
2011-07-08 00:00:00.000
2011-07-09 00:00:00.000
2011-07-10 00:00:00.000
2011-07-11 00:00:00.000
2011-07-12 00:00:00.000
2011-07-13 00:00:00.000
2011-07-14 00:00:00.000
2011-07-15 00:00:00.000
2011-07-16 00:00:00.000
2011-07-17 00:00:00.000
2011-07-18 00:00:00.000
2011-07-19 00:00:00.000
2011-07-20 00:00:00.000
2011-07-21 00:00:00.000
2011-07-22 00:00:00.000
2011-07-23 00:00:00.000
2011-07-24 00:00:00.000
2011-07-25 00:00:00.000
2011-07-26 00:00:00.000
2011-07-27 00:00:00.000
2011-07-28 00:00:00.000
2011-07-29 00:00:00.000
2011-07-30 00:00:00.000
2011-07-31 00:00:00.000

(31 row(s) affected)

So considering this set, we need only pull out the Wednesdays (or whatever) and count them.  We can pull out the “Wednesdays” one of two ways:  by using a filter with either DATENAME or DATEPART – in this case we’re expressing the weekday in “name” format so DATENAME makes the most sense – also, the number DATEPART returns depends on the DATEFIRST setting.  Once we find the weekday, we can index them with a row number:


DECLARE @dt DATETIME = GETDATE();
DECLARE @dow VARCHAR(100) = 'Wednesday';
DECLARE @num INT = 3;

SELECT DATEADD(DAY,number-1,@dt) AS MonthDate,
       ROW_NUMBER() OVER(ORDER BY number) AS DayIndex
FROM MASTER..spt_values
WHERE number > 0
    AND TYPE = 'P'
    AND DATEADD(DAY,number-1,@dt) >= @dt
    AND DATEADD(DAY,number-1,@dt) < DATEADD(MONTH,1,@dt)
    AND DATENAME(dw,DATEADD(DAY,number-1,@dt)) LIKE @dow + '%'

MonthDate               DayIndex
----------------------- --------------------
2011-07-06 00:00:00.000 1
2011-07-13 00:00:00.000 2
2011-07-20 00:00:00.000 3
2011-07-27 00:00:00.000 4

(4 row(s) affected)

We now have only the Wednesdays of the month, appropriately indexed.  All we have to do now is filter for the 3rd Wednesday.  Since we cannot use a windowing function in a where clause, we have to wrap this query in a CTE or some structure in order to be able to apply our filter:

DECLARE @dt DATETIME = GETDATE();
DECLARE @dow VARCHAR(100) = 'Wednesday';
DECLARE @num INT = 3;

WITH MonthDays AS
(
   SELECT  DATEADD(DAY,number-1,@dt) AS MonthDate,
           ROW_NUMBER() OVER(ORDER BY number) AS DayIndex
   FROM MASTER..spt_values
   WHERE number > 0
       AND TYPE = 'P'
       AND DATEADD(DAY,number-1,@dt) >= @dt
       AND DATEADD(DAY,number-1,@dt) < DATEADD(MONTH,1,@dt)
       AND DATENAME(dw,DATEADD(DAY,number-1,@dt)) LIKE @dow + '%'
)
SELECT MonthDate
FROM MonthDays
WHERE DayIndex = @num

MonthDate
-----------------------
2011-07-20 00:00:00.000

(1 row(s) affected)

So we’re done, right?  Well, there’s still the matter of getting it into one statement – it’s two statements now, not counting the declaration/assignments – you can factor the part that strips off the time and normalizes the root date into the statement, but it looks pretty messy…

DECLARE @dt DATETIME = GETDATE();
DECLARE @dow VARCHAR(100) = 'Wednesday';
DECLARE @num INT = 3;

WITH MonthDays AS
(
   SELECT  DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) AS MonthDate,
           ROW_NUMBER() OVER(ORDER BY number) AS DayIndex
   FROM MASTER..spt_values
   WHERE number > 0
       AND TYPE = 'P'
       AND DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) >= DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)
       AND DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) < DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))
       AND DATENAME(dw,DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))) LIKE @dow + '%'
)
SELECT MonthDate
FROM MonthDays
WHERE DayIndex = @num


And voila! – the answer in one statement.  Here is how I would do it in the form of a function.  I use a second CTE here to clean up some of the mess while still finding the answer in a single statement (there is also an alternate way of filtering for the current month here – normally you shouldn’t apply a function to the “left” side of a search argument, but a) we know that this is a small set and b) we have to apply DATENAME anyway, so we will definitely be scanning):

CREATE FUNCTION nth_weekday_of_month_fn (
   @dt DATETIME,
   @dow VARCHAR(10),
   @num INT
)
RETURNS DATETIME
AS
BEGIN

SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Wednesday',3)