Date of Nth weekday of a month

from the Artful Common Queries page

The date of the nth weekday (Monday or Tuesday or ...) in a month is ...
  • the date of the first day of that month, call it date1st, plus
  • the difference between weekday and the weekday of date1st, plus
  • if weekday < n, then n, else n-1, multiplied by 7.
Note that for the MySQL function Weekday(), Monday=0, Tuesday=1 etc. Our function will return null if the calculated date falls in a month that differs from the month specified. 
drop function if exists dateofnthwkday;
delimiter go
create function dateofnthwkday( yr smallint, mo smallint, wk tinyint, day tinyint ) returns date
begin
  declare ret date default null;
  set @date1st = Concat( yr, '-' , if(mo<10,'0',''), mo, '-01' );
  set @adj = Weekday(@date1st);
  set @adj = day-@adj + 7 * ( wk-if(day<@adj,0,1) );
  set ret = Date_Add( @date1st, Interval @adj Day );
  return if( month(ret)<>mo, null, ret );
end;
go
delimiter ;
-- TEST
select dateofnthwkday( 2018, 9, 1, 0 ) as 'Date of Labour Day 2018';
+-------------------------+
| Date of Labour Day 2018 |
+-------------------------+
| 2024-09-03              |
+-------------------------+
Here are test calls for the fourth week of March 2018 ... 
set @mo=3, @wk=4;
set @1st=Weekday( Concat( '2018-' , if(@mo<10,'0',''), @mo, '-01' ) );
select @mo, @wk, @1st;
+------+------+------+
| @mo  | @wk  | @1st |
+------+------+------+
|    3 |    4 |    3 |
+------+------+------+
select dateofnthwkday( 2018, @mo, @wk, 0 ) as mon,
       dateofnthwkday( 2018, @mo, @wk, 1 ) as tue, 
       dateofnthwkday( 2018, @mo, @wk, 2 ) as wed, 
       dateofnthwkday( 2018, @mo, @wk, 3 ) as thu, 
       dateofnthwkday( 2018, @mo, @wk, 4 ) as fri, 
       dateofnthwkday( 2018, @mo, @wk, 5 ) as sat, 
       dateofnthwkday( 2018, @mo, @wk, 6 ) as sun;
+------------+------------+------------+------------+------------+------------+------------+
| mon        | tue        | wed        | thu        | fri        | sat        | sun        |
+------------+------------+------------+------------+------------+------------+------------+
| 2024-03-26 | 2024-03-27 | 2024-03-28 | 2024-03-22 | 2024-03-23 | 2024-03-24 | 2024-03-25 |
+------------+------------+------------+------------+------------+------------+------------+

Last updated 30 Mar 2024