Re: HELP WITH SQL DATE FUNCTIONS

In article <C3BKts.1nL_at_news.ucs.mun.ca> daren_at_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 Received on Wed Mar 03 1993 - 21:23:02 CET