Nth weekday of the month

Question

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?

Answer 1

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

Answer 2

This would give the number of the week (which is actually the same as if it is the 5th friday or 5th Monday)

SELECT WEEK(dateasd,5) - 
WEEK(DATE_SUB(dateasd, INTERVAL DAYOFMONTH(dateasd)-1 DAY),5)
from test

Use Weekday() function to find which day it is

Fiddle at http://www.sqlfiddle.com/#!2/7a8b6/2/0