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;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.
+----------------------------+
| d |
+----------------------------+
| Saturday, August 1st, 2009 |
+----------------------------+
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.
1 comments:
I like to wrap that type of stuff in a procedure or function..
Sloppy example, your query is cleaner..
delimiter $$
drop procedure if exists next_wednesday $$
create procedure next_wednesday(a_in char(9))
begin
declare date_hack1 varchar(50);
declare date_num_in , date_num, inc int;
set date_hack1 := concat("200442 ", a_in);
SELECT dayofweek(now()) into date_num;
SELECT dayofweek(STR_TO_DATE(date_hack1, '%X%V %W'))into date_num_in;
IF date_num_in > date_num
THEN SELECT date_format(now()+ interval date_num_in - date_num day, '%W %M %D') as next_day;
ELSE SELECT date_format(now()+ interval (date_num_in - date_num +7) day , '%W %M %D') as next_day;
END IF;
end $$
delimiter ;
mysql> call next_wednesday("Thursday");
+----------------------+
| next_day |
+----------------------+
| Thursday August 13th |
+----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Post a Comment