Hi there, I've been trying around for some time now and i just don't see a solution to my problem:
I have a table called "feiertage" ("Holidays"):; mysql> select * from feiertage;explain feiertage; +------------+ | datum | +------------+ | 2001-12-24 | | 2001-12-25 | | 2001-12-26 | | 2002-01-01 | +------------+ 4 rows in set (0.81 sec) +-------+------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+------------+-------+ | datum | date | | PRI | 0000-00-00 | | +-------+------+------+-----+------------+-------+ 1 row in set (0.81 sec) now i want to know the last date, that is not listed in that table and that is not on a weekend and is earlier or the same than a given date: assume 2001-12-29 is the given date, then i tried around with a loop in perl increasing the INTERVAL. select DATE_SUB('2001-12-29', INTERVAL 0 DAY) as tag from feiertage where datum = DATE_SUB('2001-12-29', INTERVAL 0 DAY) or WEEKDAY(DATE_SUB('2001-12-29', INTERVAL 0 DAY))>4; tells me that this date is on a weekend or a holiday. that doesn't rellay help me, what i need is a query that gives me "2001-12-28" (which is the friday). i played around for 2 hours now but have no idea how to make it work with just one query. I could think of ways to do it with perl and loops and stuff like that, increasing the INTERVAL until i get zero rows which would mean i stepped backwards enough days, but then i would need another query to actually get the date in the right form "2001-12-28" which i need. But i have still hope there is a way without the overhead of loops and stuff... has anybody some suggestion? thanks a lot for any hints Richard -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net --------------------------------------------------------------------- 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