Date of last 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 Thursday before or after a given date.

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

In pseudocode ... 

  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 |
+------------+----------+-------------+---------------+
| 2024-11-10 | Saturday |           7 | 2024-11-15    |
+------------+----------+-------------+---------------+
Similar logic works for the Thursday before a given date.

But we don't wish to be confined to asking about Thursdays. We need a simple generalisation to any target day. One way is to create a lookup string of comma-separated weekday names ...

set @wkdays = "sun.mon,tue,wed,thu,fri,sat";
Now, given a weekday name eg "Thursday", its weekday number is ... 
   1 + floor( locate( left("Thursday",3), @wkdays ) / 4 )
With that bit of arithmetic, we can compute the date for any previous or next weekday relative to a given date, say Wed 21 Aug 2024 ...

set @wkdays = "sun.mon,tue,wed,thu,fri,sat";
select 
  @d := date('2024-8-21') as 'base date',  
  @dn := dayofweek(@d) as 'base dayofweek',
  @dname := dayname(@d) as 'base dayname',
  @tname := "Thursday" as 'target day',
  @tn := 1 + floor( locate( left(@tname,3), @wkdays ) / 4 ) 
    as 'dayofweek target';
select subdate( @d, 
                if( @tn=@dn, 7, 
                    if( @dn>@tn, @dn-@tn, 7+@dn-@tn ) 
                  ) 
              ) as "Thursday before 2024-8-21";
+---------------------------+
| Thursday before 2024-8-21 |
+---------------------------+
| 2024-08-15                |
+---------------------------+
select adddate( @d, 
                if( @tn=@dn, 7, 
                    if( @tn>@dn, @tn-@dn, 7+@tn-@dn ) 
                  ) 
              ) as "Thursday after 2024-8-21";
+--------------------------+
| Thursday after 2024-8-21 |
+--------------------------+
| 2024-08-22               |
+--------------------------+
As a function ... 
drop function if exists date_of_weekday;
delimiter go
create function date_of_weekday( 
  pbasedate date, ptargetday varchar(9), pdirection varchar(7) 
) returns date
begin
  declare wkdays varchar(27) 
    default "sun.mon,tue,wed,thu,fri,sat";
  declare dn, tn tinyint;
  declare ret date default null;
  set dn = dayofweek(pbasedate);
  set tn = 1 + floor( locate( left(ptargetday,3), wkdays ) / 4 );
  if left(pdirection,4)='prev' then
    set ret = subdate( pbasedate, 
                       if( tn=dn, 7, 
                           if( dn>tn, dn-tn, 7+dn-tn ) 
                         ) 
                     );
  elseif left(pdirection,4)='next' then
    set ret = adddate( pbasedate, 
                       if( tn=dn, 7, 
                           if( tn>dn, tn-dn, 7+tn-dn ) 
                         ) 
                     );
  end if;
  return ret;
end;
go
delimiter ;

Last updated 18 Aug 2024