Nth weekday of the month

mysql

Solution

To find which "nth" a given day is is rather easy:

select (case when day(table_date) between 1 and 7 then 1
             when day(table_date) between 8 and 14 then 2
             when day(table_date) between 15 and 21 then 3
             when day(table_date) between 22 and 28 then 4
             else 5
        end) as NthDay

You can also do this using "integer" arithmetic:

select 1 + floor((day(table_date) - 1) / 7) as NthDay

Problem

Pseudo code of what I'd like to do: ``` SELECT * FROM table WHERE NTH_DAY(DATE(table_date)) = NTH_DAY($input_date); ``` I want to determine what the nth weekday of the month is for a given date. For example, if given the input "2013-08-30" it should return 5, since it is the fifth occurrence of that weekday (Friday) in the month. I've been reading through countless similar questions but the majority are looking for just the opposite, for example they want to find the date of the fifth Friday, whereas I want to determine what the nth number of a date is. Other questions appear to be what I'm after but they're in different programming languages I don't understand. Can someone please explain if this is possible in a MySQL query and how to do it?

Original source

Related problems