Get the Iteration of a Weekday in a Month on a Virtual Calendar

There are many situations where you need to identify the iteration of a weekday within a given month. Most commonly though is for the identification of public holidays e.g., the 4th Thursday in November - Thanksgiving in USA.

There are no built-in methods to get this information in any SQL-based platform I have used thus far – if you know of one, please let me know.

So, I created a table-valued function to extract this information from a given Year and Month. However, in order to do this, we need a base calendar table to work with. So, as a bonus, I have included a table-valued function to generate a virtual calendar table we can use for this purpose.

Key aspects of the virtual date table

• It will generate a list of dates between the given start date and end date.

• It uses a succession of cross joins to generate the list of rows and then calculates the dates from that. The original idea for this row number generation came from itprotoday.

  • It starts by creating a 2-row table from hardcoding a SELECT that unions with another SELECT

  • From there it CROSS JOINs the table to itself to increase the number of rows by an exponent of 2.

  • This pattern repeats 6 times to generate a large list of rows.

Key Aspects Of The Function

• The output table provides all instances of the weekday in the month e.g. the First, Second, Third, Fourth, Fifth and Last iteration as a single row table.

• If the weekday only occurs four times, the Fifth column returns null.

• You need to provide the Year and Month range.

• You need to provide the week day name to extract e.g. Monday, Tuesday etc.

• This function relies on the virtual date table above. However, if you have a calendar table already, it can be adapted to read from that instead.