I couldn’t wait, and so I am continuing this topic a might sooner than expected (the original entry can be found here). In that first entry, I had spoken about using T-SQL to find the Nth weekday of a month given a date in that month. Without further ado (pun intended), here is the UDF to do just that:

CREATE FUNCTION fnNthWeekdayOfMonth

(@DateInMonth DateTime, @NthWeek TinyInt, @Weekday TinyInt)

RETURNS DateTime

AS

BEGIN

-- if weekday is invalid or if week is invalid for specified month, return null

IF (NOT (@Weekday Between 1 And 7)

AND (@NthWeek > Ceiling(Day(DateAdd(d, -Day(DateAdd(m, 1, @DateInMonth)), DateAdd(m, 1, @DateInMonth))) / 7.0 )))

RETURN NULL

-- Find first day of month for specified date

SET @DateInMonth = DateAdd(d, -Day(@DateInMonth) + 1 , @DateInMonth)

-- Determine how many days to add to 1st of month to get the target date

DECLARE @DaysToAdd TinyInt

SET @DaysToAdd = 7*(@NthWeek - 1) +

(@Weekday - DatePart(weekday, @DateInMonth)) +

Sign(Sign(@Weekday - DatePart(weekday, @DateInMonth)+ 1) - 1) * -7

-- If target date is past end of month, return null

IF (@DaysToAdd >= Day(DateAdd(d, -Day(DateAdd(m, 1, @DateInMonth)), DateAdd(m, 1, @DateInMonth))))

RETURN NULL

-- All is well, return the date

RETURN DateAdd(d, @DaysToAdd , @DateInMonth)

END

The concept is really straightforward; it’s the implementation that is more complex. Calculating the number of days to add to the first of the month is the really the only tough item here. I will explain how it works using real data: Looking at the calendar for January 2004, you will see that the 1st of the month is on a Thursday. Say you want to find the 2nd Tuesday (weekday value = 3) of the month (that would be the 13th): @DaysToAdd will need to be 12 in order to get the correct date (1st of the month + 12 = 13th). The first step is to get a number equal to the number of days in the 1 less than the desired number of weeks. The code subtracts 1 from @NthWeek value, multiplies the result by 7, and adds one. Using the example, that would be 7 * (2 – 1), which would equal 7. Remember that number.

Next, the weekday value of the first of the month is subtracted from the desired weekday. Using the example, this would be 3 - 5, which would result in -2. Add that to the value from before (did you forget about the 7 already?) to give 5 (now forget about the 7 and remember the 5 instead).

This value represents the number of days that will be either: The number we want or 7 less than the number we want; the results depend on if the desired weekday is before (7 less), the same as (7 less) or after (the exact amount) the weekday of the 1st of the month.

Finally, the last calculation is similar to the previous one and can be seen here using the example data:

Sign(Sign(3 – 5 + 1) - 1) * -7 *results in*

Sign(Sign(-1) - 1) * -7 *results in*

Sign(-1 - 1) * -7 *results in*

Sign (-2) * -7* results in*

-1 * -7 *results in *

7

Take this 7 (which is really the number of days from the 1st of the month until the ) and add it to the 5 and you get… tada… 12. This last number is 7 when the desired weekday is on or before the weekday of the 1st of the month and 0 when it is after.

If you’re thinking that I could have used an if statement and kept the complexity of the assignment of @DaysToAdd to a minimum, and you’re are correct. The following could have replaced the more complicated assignment:

IF Sign(@Weekday - DatePart(weekday, @DateInMonth)) = 1

SET @DaysToAdd = 7 * @NthWeek - 1) + (@Weekday - DatePart(weekday, @DateInMonth))

ELSE

SET @DaysToAdd = 7 * @NthWeek + (@Weekday - DatePart(weekday, @DateInMonth))

I also could have gone the other extreme and replaced everything from the assignment of @DateInMonth to be the first of the month up through the assignment of @DaysToAdd with a single line of code, shown here:

SET @DaysToAdd = 7 * (@NthWeek - 1) + (@Weekday - DatePart(weekday, DateAdd(d, -Day(@DateInMonth) + 1 , @DateInMonth))) + Sign(Sign(@Weekday - DatePart(weekday, DateAdd(d, -Day(@DateInMonth) + 1 , @DateInMonth))+ 1) - 1) * -7

The point here is that there are many possible solutions, of which I am showing only one - well, actually three because you ask so many questions. ;-)

## Comments