The date of next Thursday

from the Artful Common Queries page


Given a date and its weekday number (1=Sunday, ..., 7=Saturday), there are three possibilities:
1. Today is Thursday: then next Thursday is 7 days from now.
2. Today is before Thursday: then next Thursday is (5 minus today's weekday number) from now.
3. Today is after Thursday: then next Thursday is 7 + (5 minus today's weekday number).

set @d=curdate();
set @n = dayofweek(curdate());
select 
  @d:=adddate(curdate(),0) as date, 
  @n:=dayofweek(adddate(curdate(),0)) as weekday, 
  adddate(@d,if(@n=5,7,if(@n<5,5-@n,7+5-@n))) as thurs;
+------------+---------+------------+
| date       | weekday | nextthurs  |
+------------+---------+------------+
| 2008-03-10 |       2 | 2008-03-13 |
+------------+---------+------------+

It's easily encapsulated in a function:

set global log_bin_trust_function_creators=1;
drop function if exists dateofnextweekday;
delimiter |
create function dateofnextweekday( d date, which tinyint ) returns date
begin
  declare today tinyint;
  set today = dayofweek(d);
  return adddate( d, if( today=which,7,if(today<which,which-today,7+which-today)));
end |
delimiter ;

While we're at it, we might as well have a function to return the most recent given weekday for a given date ...

delimiter go
create function dateoflastweekday( d date, which tinyint ) returns date
begin
  declare today tinyint;
  set today = dayofweek(d);
  return adddate( d, if(today=which,-7,if(today>which,which-today,which-today-7) ));
end; 
go
delimiter ; 


Return to the Artful Common Queries page