The date of next Thursday

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 ;