Hi all, Can anyone tell me how to do this?
What I want to do is run a query that calculates the next [n]th [Sun - Sat] of [Jan - Dec] in MySQL? ie (Return the next occurance of the 2nd Tuesday in April). In my table I store the day of the week I want the event to occur on, which occurance in the month of that weekday, and what month I want it to recur for. Those values are all stored as numbers (DayOfWeek -> 1 - 7, Which Occurance -> 1-4, Month -> 1 - 12). For these examples assume the current date is Wed, Oct 9, 2002. If I wanted to know the next occurance of the 3rd Thursday in December (ie repeattype = C, repeatinterval = 3, repeatday = 5, repeatmonth = 12), it would return a date of Dec 19, 2002. But if I wanted to know the next occurance of the 2nd Monday in April (ie repeattype = C, repeatinterval = 2, repeatday = 2, repeatmonth = 4), it would return a date of Apr 12, 2003. This is for an event tracking system which needs to allow for recurring events. The table format is like this: mysql> describe events; +----------------+-------------+------+-----+---------+--------------------- ----------------------- | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+--------------------- -----------------------+ | eventnumber | int(11) | | PRI | NULL | auto_increment | | title | varchar(50) | YES | | NULL | | | eventdate | date | YES | | NULL | | | starttime | time | YES | | NULL | | | endtime | time | YES | | NULL | | | notes | longtext | YES | | NULL | | | location | longtext | YES | | NULL | | | repeatuntil | date | YES | | NULL | | | repeattype | char(1) | YES | | NULL |None, Daily, Weekly, Monthly, Yearly, Custom| | repeatinterval | int(11) | YES | | NULL |1 through 4 | | repeatday | int(11) | YES | | NULL |1 through 7 | | repeatmonth | int(11) | YES | | NULL |1 through 12 | +----------------+-------------+------+-----+---------+--------------------- -----------------------+ Any takers? --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
