"Stembridge, Michael" <[EMAIL PROTECTED]> wrote on 03/22/2005 10:30:50 AM:
> 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 > Do a little pre-processing to get your "target" date. I am assuming a 5 day workweek (M-F) and no holidays. SET @TodaysDate = CURDATE(); SET @LastBusDayStart = IF(DAYOFWEEK(@TodaysDate)=1 , @TodaysDate - INTERVAL 2 DAY , IF(DAYOFWEEK(@TodaysDate)=2 , @TodaysDate - INTERVAL 3 DAY , @TodaysDate - INTERVAL 1 DAY ) ); SET @LastBusDayEnd = @TodaysDate + INTERVAL 1 DAY; Then your new, very fast (because you can use an index) query looks like: SELECT id FROM table WHERE datetime_field >= @LastBusDayStart and datetime_field < @LastBusDayEnd; The 40 or 50 milliseconds it takes to compute the starting and ending dates in the SET clauses saves you <insert "huge quantity" noun here> of time because you move the calculation OUT of your query. The query only needs to evaluate against constant values and will not need to compute a date difference for each row as it would have had to do using your original query. IF you are on a version of MySQL pre-3.23 then these calculations get a little more hairy but they are still possible. Shawn Green Database Administrator Unimin Corporation - Spruce Pine