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   |
+------------+-------------+---------------+---------------+


Return to the Artful Common Queries page