Newsgroups: comp.databases.oracle From: blah@maas-neotek.arc.nasa.gov (MJW/TWF) Subject: Re: HELP WITH SQL DATE FUNCTIONS Message-ID: <1993Mar3.202302.5262@kronos.arc.nasa.gov> Sender: usenet@kronos.arc.nasa.gov (Will Edgington, wedgingt@ptolemy.arc.nasa.gov) Nntp-Posting-Host: maas-neotek.arc.nasa.gov Organization: NASA/ARC Information Sciences Division References: Date: Wed, 3 Mar 1993 20:23:02 GMT Lines: 54 In article daren@morgan.ucs.mun.ca (Daren Janes) writes: >I have a need to calculate the number of Monday..Fridays >between two dates. There isn't a function that I could see >in the documentation. I have it working with a PL/SQL >procedure that sequentially passes through the days in question >and checks to see if its a Mondat..Friday. This is rather CPU >intensive and I was wondering if there is a better way. [I tried to mail a response, but our mailer has been coughing a lot.] Well, you piqued my curiousity, so I had to give this a shot. Hell, it actually works and is very fast: assume you want to know from 2-FEB-92 to 12-DEC-93. SELECT days - (2 * TRUNC (days/7)) - DECODE (MOD(days,7), 0, 0, DECODE (first_day, 1, 1, DECODE (SIGN(MOD(days,7) + first_day - 8), -1, 0, 0, 1, 1, 2))) FROM dual; in the above, replace days with: (TO_DATE ('12-DEC-93') - TO_DATE ('02-FEB-92') + 1) The +1 is so you include the starting date as well. first_day with: TO_CHAR (TO_DATE ('02-FEB-92'), 'D') The premise behind this is simple. To determine the number of particular days between 2 dates, you first find the number of weeks. Obviously, you will have 5 days per week. Then, you only need to disambiguate the MOD remainder of those two dates. The approach I took was to take all of the days and subtract off weekends, since there is only 2 days. If you want more detail, I have it, just ask. The key to adding extra days is based on the first date, and is a non-continuous function: if first_date is a 1 (Sunday), then add 1 day, otherwise if mod_remainder + first_date - 8 is < 0 add 0 days is = 0 add 1 day is > 0 add 2 days That's it! Mark