MySQL Forums
Forum List  »  General

Re: create date from #of day week
Posted by: Sean Nolan
Date: April 04, 2005 12:05AM

I think this will do it

SELECT DATE_ADD(<<date>>, INTERVAL ((<<weekday>> - WEEKDAY(<<date>>)) + 7) % 7 DAY);

This should return the next date that is the specified day of the week. You need to substitute a day of week number in place of <<weekday>>, given the following numbers
0 - Monday
1 - Tuesday
2 - Wednesday
3 - Thursday
4 - Friday
5 - Saturday
6 - Sunday
And you substitute the two occurences of <<date>> with your date.

So today (4/4/2005) if I run it with 5 as the day of week and use the curdate() function to get the current date

SELECT DATE_ADD(curdate(), INTERVAL ((5 - weekday(curdate())) + 7) % 7 DAY);

I get 4/9/2005 for the date of next Saturday
+----------------------------------------------------------------------+
| date_add(curdate(), INTERVAL ((5 - weekday(curdate())) + 7) % 7 DAY) |
+----------------------------------------------------------------------+
| 2005-04-09 |
+----------------------------------------------------------------------+


Sean

Options: ReplyQuote


Subject
Written By
Posted
April 02, 2005 07:30AM
Re: create date from #of day week
April 04, 2005 12:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.