CODEWITHSUNDEEP
mysqlsql
Viny Machado
Viny Machado

Reputation: 629

MySQL: Get date based on the nth day of the week on a particular month/year

I need to recover/create a date based on the nth day of the week of any given year and month.

Eg. I want to know which day will be the third Monday of December 2020.

Any suggestions?

Upvotes: 0

Views: 238

Answers (2)

Akina
Akina

Reputation: 42632

Test the expression:

SELECT (@year*10000 + @month*100 + 1) + INTERVAL 7 * @weeknumber + @weekday - WEEKDAY(@year*10000 + @month*100 + 1) - 7*(@weekday > WEEKDAY(@year*10000 + @month*100 + 1) - 1) DAY

where

@year - the year (2020 in the question example)

@month - the month (12)

@weekday - needed weekday (0) (0-Monday,1-Tuesday,...6-Sunday)

@weeknumber - the number of needed day in the month (3)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

One method is brute force method. Generate all the dates in the month and choose the one you want:

with recursive dates as (
      select date('2020-12-01') as dte
      union all
      select dte + interval 1 day
      from dates
      where dte < last_day(dte)
    )
select *
from (select d.*, dayname(dte) as dname,
            row_number() over (partition by dayname(dte) order by dte) as cnt
      from dates d
     ) d
where cnt = 3 and dname = 'Monday';

You could, of course, use a calendar table instead. This approach makes it easy to extend the request -- the first month and the fourth Thursday, if that is what you really want.

Here is a db<>fiddle.

Upvotes: 0

Related Questions