CODEWITHSUNDEEP
mysql
mister martin
mister martin

Reputation: 6252

Nth weekday of the month

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?

Upvotes: 3

Views: 3066

Answers (5)

Luuk
Luuk

Reputation: 14899

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.

Using MySQL 8.0, you can do:

WITH RECURSIVE dates AS (
   SELECT CAST('2023-01-01' as DATE) as D
   UNION ALL
   SELECT DATE_ADD(D, INTERVAL 1 DAY) FROM dates WHERE d<='2023-12-31'
)
SELECT 
    d,
    day(d) as day,
    DATE_FORMAT(d,'%w') as weekday,
    rank() over (partition by DATE_FORMAT(d,'%m'),DATE_FORMAT(d,'%w') order by d) as nth_weekday
FROM dates 
WHERE DATE_FORMAT(d,'%Y')=2023
ORDER BY d
;

see: DBFIDDLE

or, for an MSSQL variant: DBFIDDLE MSSQL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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

Upvotes: 5

Abdul Qadir Memon
Abdul Qadir Memon

Reputation: 1019

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.

Upvotes: 1

techdude
techdude

Reputation: 1334

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.

Upvotes: 1

skv
skv

Reputation: 1803

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

Upvotes: 1

Related Questions