From: Peter Brawley Date: March 22 2005 6:01pm Subject: Re: SELECT rows from the previous business day List-Archive: http://lists.mysql.com/mysql/181586 Message-Id: <42405D79.9000304@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-42405D795C64=======" --=======AVGMAIL-42405D795C64======= Content-Type: multipart/alternative; boundary=------------070607050400020007070105 --------------070607050400020007070105 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Michael, Someone on the list recently dave this quickie formula for computing the no. of biz days:: DATEDIFF(date2, date1) - (WEEK(date2) - WEEK(date1)) * 2 PB ----- Stembridge, Michael wrote: >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 > > > > >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 3/21/2005 > > --------------070607050400020007070105 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Michael,

Someone on the list recently dave this quickie formula for computing the no. of biz days::

DATEDIFF(date2, date1) - (WEEK(date2) - WEEK(date1)) * 2

PB

-----

Stembridge, Michael wrote:

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


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 3/21/2005

--------------070607050400020007070105-- --=======AVGMAIL-42405D795C64======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 3/21/2005 --=======AVGMAIL-42405D795C64=======--