A quick try: In most cases you need results from yesterday, but for Sunday and Monday we must correct extra for the weekend.
DAYOFWEEK() returns 1 for Sunday, 2 for Monday, etc., so we can use this to make a correction value: SET @COR = (DAYOFWEEK(NOW()) < 3) * DAYOFWEEK(NOW()); SELECT * FROM table WHERE `datetime` > CURDATE() - INTERVAL (1 + @COR) DAY AND `datetime` < CURDATE() - INTERVAL @COR DAY; This construction is index-friendly, because MySQL will reduce the calculation to a constant before running the query. The result is a simple col > constant expression which can make use of indexes if they are present. Expressions such as TO_DAYS(`datetime`) (as suggested by you) will result in a full table scan and slow down the query significantly. If you consider Saturday as a business day you only need to make a correction for Monday: SET @COR = (DAYOFWEEK(NOW()) = 2); Regards, Jigal. ----- Original Message ----- From: "Stembridge, Michael" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Tuesday, March 22, 2005 4:30 PM Subject: SELECT rows from the previous business day > A table exists with id and datetime columns; I need to SELECT records from > the previous business day. I began with this: > > > > SELECT id FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(datetime) <=1 > > > > But if NOW() is a Monday, it pulls records from Sunday (there are none since > records are inserted M-F only). I thought of using something like this in > my WHERE clause: > > > > "AND DAYOFWEEK(datetime) != 6" > > > > Though this doesn't seem like an operable solution (If I'm not mistaken this > would return 0 rows if no records were inserted on a Sunday). > > > > Does MySQL include a specifier for business day? > > > > Thank you, > > Michael > > > > !DSPAM:42403a4317126025714369! > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
