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?

Honestly, if you need to do that sort of query, I would simply build a table of all dates from which you can do that lookup. Similar to how one would create a date dimension table in a data warehouse. Something like this https://gist.github.com/johngrimes/408559 Read here for more on dimension tables (see Common Patterns section) http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29 — Mike Brant, Aug 21 '13 at 18:56

score 5 · Accepted Answer · answered Aug 21 '13 at 18:55

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 "remainder" arithmetic:

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

score 1 · Answer 2 · answered Jul 03 '15 at 21:30

I'm going to throw my two cents in here with a third option that gets it based on finding the 1st of each day, and then adding multiples of 7 to get to the final result.

Function

CREATE FUNCTION `ISNTHDAYINMONTH`(checkDate DATE, weekNumber INTEGER, dayOfWeek INTEGER, monthOfYear INTEGER) RETURNS INTEGER
    NO SQL
    DETERMINISTIC
BEGIN
    DECLARE firstOfMonth DATE; 
    SET firstOfMonth = DATE_SUB(checkDate, INTERVAL DAYOFMONTH(checkDate) - 1 DAY); #Find the first day of the current month
    IF DAYOFWEEK(checkDate) = dayOfWeek AND MONTH(checkDate) = monthOfYear #Make sure at least this matches
        AND DAYOFMONTH(checkDate) = ((1 + (7 + dayOfWeek - DAYOFWEEK(firstOfMonth)) % 7) + 7 * (weekNumber - 1)) THEN #When the date matches the nth dayOfWeek day of the month
        RETURN 1;
    ELSE
        RETURN 0; #Nope
    END IF;
END

Use

SELECT ISNTHDAYINMONTH(datecol, weekNumber, dayOfWeek, monthOfYear);

Where weekNumber is 1 through 5, dayOfWeek is 1 through 7 (1 = Sun), and monthOfYear is 1 through 12.

Example

To check if datecol is the second Tuesday of April:

SELECT ISNTHDAYINMONTH(datecol, 2, 3, 4);

Calculations

Let's break it down. This line gets the first day of the month for the date that is passed in.

SET firstOfMonth = DATE_SUB(checkDate, INTERVAL DAYOFMONTH(checkDate) - 1 DAY); #Find the first day of the current month

This check ensures that the date has the correct day of the week, and correct month (just in case the user passed in a date that isn't even the right day of the week).

DAYOFWEEK(checkDate) = dayOfWeek AND MONTH(checkDate) = monthOfYear

Now for the big one:

DAYOFMONTH(checkDate) = ((1 + (7 + dayOfWeek - DAYOFWEEK(firstOfMonth)) % 7) + 7 * (weekNumber - 1))

To get the amount we need to add to 1 to get the date, we calculate dayOfWeek that we are looking at, minus the day of the week that the first day of the month falls on to get the offset mod 7 ((dayOfWeek - DayOfWeek(first)) % 7).

Note that because the first of the month could land on a day before or on the day we are looking at, we add an additional 7 and then mod by seven. This is needed because MySQL's Mod function does not properly compute mod. I.e. -1 % 7 should be 6, but MySQL returns -1. Adding 7 and taking the modulus ensures the result will always be correct.

To summarize:

NumberToAddToOneToGetDateOfFirstWeekDay = (7 + DayOfWeek - DayOfWeek(firstDayOfMonth)) %

Then we add one and how every many multiples of 7 are needed to get to the correct week.

score 1 · Answer 3 · answered Dec 20 '19 at 11:03

1

select ceiling(DATEPART(day, GETDATE())/7.0)

This will always give you nth occurrence number of current day in a month. For example, if the current day is Friday, and it occurred 3rd time this month. It will return 3. If you replace GETDATE() with your Date variable or column name it will return the occurrence of the day on that date in the corresponding month.

answered Dec 20 '19 at 11:03

Abdul Qadir Memon

950
1
12
27

This solution is impossible in MySQL. DATEPART() AND GETDATE() functions don't exist in MySQL. – EliteRaceElephant Sep 04 '22 at 16:34

skv · Answer 4 · 2013-08-22T04:17:33.140

0

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

edited Aug 22 '13 at 04:17

answered Aug 21 '13 at 18:55

skv

1,793
3
19
27

I'm not sure I follow your logic here. The example you provided enters the date 2013-08-21 into the database and your query returns 4. That date is the 3rd Wednesday, so I am looking for it to return 3. – mister martin Aug 21 '13 at 19:13
The +1 was an issue, the week function basically returns the week of the year and then I find the week of the first day of the month and deduct it, that is the logic – skv Aug 22 '13 at 04:18

Nth weekday of the month

4 Answers4