I have some bad news and good news. The bad: there is no built in MySQL function for finding a relative date. The good: it's still pretty trivial to get MySQL to calculate it.

The trick is that you need to start with a known date, such as:

mysql> SELECT DATE_FORMAT(CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) DAY, '%W, %M %D, %Y') AS d;
+----------------------------+
| d                          |
+----------------------------+
| Saturday, August 1st, 2009 |
+----------------------------+

That gets you the date for the Saturday that ends the previous week. Then "this Wednesday" or "last Thursday" or almost any other relative date is simple to calculate, provided one caveat. You must do one additional check to see if were already passed the target day of the week.

If today is Tuesday, then "this Wednesday" is 4 days from our reference date above. However if it's already Thursday of this week, then we have to add 7+4 days instead.

mysql> set @dayofweek=4;
mysql> SELECT DATE_FORMAT(CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) DAY + INTERVAL (DAYOFWEEK(CURDATE())>=@dayofweek)*7+@dayofweek DAY, '%W, %M %D, %Y') AS d;
+-----------------------------+
| d |
+-----------------------------+
| Wednesday, August 5th, 2009 |
+-----------------------------+


For "next Wednesday," you only have to add another week.

For dates such as "last Monday" the process is similar:

mysql> set @dayofweek=2;
mysql> SELECT DATE_FORMAT(CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) DAY + INTERVAL (DAYOFWEEK(CURDATE())<=@dayofweek)*-7 + @dayofweek DAY, '%W, %M %D, %Y') AS d;
+--------------------------+
| d |
+--------------------------+
| Monday, August 3rd, 2009 |
+--------------------------+

Note that it's not necessary to do this using SET @dayofweek=N; That is simply to make the query a little more readable in this case.