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
Subject
Written By
Posted
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.