## Date of last Wednesday or next Thursday

### from the Artful Common Queries page

 Sometimes we need to calculate the date of the most recent or next specific weekday, say the date of the Wednesday before a given date, or the next Thursday after it. Given a date and its weekday number as returned by the MySQL function DayOfWeek(), 1=Sunday ... 7=Saturday, there are three possibilities for calculating the date of next Thursday: 1. Today is Thursday, so next Thursday is 7 days from now. 2. Today is before Thursday, the day number of Thursday is 5, so next Thursday is (5 minus today's weekday number) days from now. 3. Today is after Thursday, so next Thursday is 7 + (5 minus today's weekday number) days from now. That is ... ```   datetoday +    if( today's weekdaynum < target weekdaynum, 0, 7 ) +    ( target weekday number - today's weekday number ) ``` In SQL ... ``` select    @d:=adddate(curdate(),0) as Today,    dayname(@d) as Weekday,   @n:=dayofweek(adddate(curdate(),0)) as "Weekday Num",    adddate(@d,if(@n=5,7,if(@n<5,5-@n,7+5-@n))) as "Next Thursday"; +------------+---------+-------------+---------------+ | Today      | Weekday  | Weekday Num | Next Thursday | +------------+----------+-------------+---------------+ | 2018-11-10 | Saturday |           7 | 2018-11-15    | +------------+----------+-------------+---------------+ ``` To prove the formula correct for all start dates and all target weekdays, write a function that implements the formula, and a test procedure that runs the formula against 7 target days each for a given week of dates: ``` drop function if exists dateOfaNextDayOfWeek; delimiter go create function dateOfaNextDayOfWeek( startdate date, targetdaynum tinyint ) returns date begin   declare startdaynum, diff tinyint;   if targetdaynum not between 1 and 7 then     return null;   else     set startdaynum = dayofweek(startdate);     set diff = if(startdaynum <= targetdaynum,0,7) + (targetdaynum-startdaynum );     return startdate + interval diff day;   end if; end; go delimiter ; drop procedure if exists testDateOfaNextDayOfWeek; delimiter go create procedure testDateOfaNextDayOfWeek( d date ) begin   declare dresult date;   declare i, j tinyint default 1;   drop table if exists testd;   create table testd( startdate date, dayofweek tinyint, targetdayofwk tinyint, result date, resultdayofwk tinyint );   repeat      set j=1;     repeat       set dresult = dateOfaNextDayOfWeek(d,j);       insert into testd values( d, dayofweek(d), j, dresult, dayofweek(dresult) );       set j=j+1;     until j > 7 end repeat;     set i=i+1, d = d + interval 1 day;   until i > 7 end repeat;   select * from testd order by startdate,targetdayofwk;   drop table testd; end; go delimiter ; call testDateOfaNextDayOfWeek(); ``` Similarly we can work out a formula for the last Wednesday before a given date: ```   datetoday date -    if( weekdaytoday <= targetweekday,    7 - ( targetweekday - weekdaytoday ),    ( weekdaytoday - targetweekday ) )  ``` and again we can test this formula against all combinations of starting and target weekdays. But must we have separate functions for previous and next weekdays? No, here's one that handles both. It has parameters for the starting date and target weekday number, also a parameter for how many instances of a weekday to go back or forward from the starting date. To go back one Wednesday, set targetdaynum to 4 and howmany to -1, for next Thursday 5 and 1 respectively, for two Thursdays hence 5 and 2 respectively, &c. The function returns null if you ask for an invalidly numbered weekday, or the start date passed to it if the number-of-weeks parameter howmany is 0, otherwise it returns the calculated date: ``` drop function if exists dateOfWeekday; delimiter go create function dateOfWeekday(    startdate date, targetdaynum tinyint, howmany smallint  ) returns date begin   declare startdaynum, diff tinyint default 0;   if targetdaynum not between 1 and 7 then     return null;   elseif howmany < 0 then     set howmany = howmany * -1, startdaynum = dayofweek(startdate);     set diff = if( startdaynum <= targetdaynum,                     7-(targetdaynum-startdaynum),                     startdaynum-targetdaynum                   ) + 7*(howmany-1);     return startdate - interval diff day;   elseif howmany > 0 then     set startdaynum = dayofweek(startdate);     set diff = if(startdaynum < targetdaynum,0,7) +                 targetdaynum - startdaynum + 7*(howmany-1);     return startdate + interval diff day;   else      return startdate;   end if; end; go delimiter ; set @d='2018-11-09'; select @d, dayname(@d), dayofweek(@d),         dateofweekday( @d, 1, 1 ) as 'Coming Sunday'; +------------+-------------+---------------+---------------+ | @d         | dayname(@d) | dayofweek(@d) | Coming Sunday | +------------+-------------+---------------+---------------+ | 2018-11-09 | Friday      |             6 |  2018-11-11   | +------------+-------------+---------------+---------------+ ```