Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL Function For Adding "Business" Days

883834Aug 22 2011 — edited Aug 22 2011
I have created a function that, given a date and a number, adds that many days to the date. The catch is that it has parameters to include Saturday, Sunday, or Monday as those days. Here is an example of the call

DAYSBETWEEN('01-AUG-11',5,0,0,0) would give you 5 Calendar days from Aug 1, 2011 (as you would not exclude Saturday, Sunday, or Monday)

DAYSBETWEEN('01-AUG-11', 5, 1,1,0) would give you 5 business days from Aug 1, 2011

DAYSBETWEEN('01-AUG-11',5,0,1,1) would give you 5 days from Aug 1, 2011 excluding Sunday and Monday.

My current function is:
create or replace
FUNCTION DAYSBETWEEN(
      DAYIN  IN DATE ,
      ADDNUM IN NUMBER ,
      EXSAT  IN NUMBER ,
      EXSUN  IN NUMBER ,
      EXMON  IN NUMBER )
    RETURN DATE
  IS
    dtestart DATE;
    intcount NUMBER;
    holidays NUMBER;
  BEGIN
    dtestart       := DAYIN;
    intcount       :=1;
    WHILE intcount <= ADDNUM
    LOOP
      dtestart     := dtestart + 1;
      IF NOT((EXSAT = 1 AND TO_CHAR(dtestart, 'd')= 7) OR (EXSUN=1 AND TO_CHAR(dtestart, 'd')= 1) OR (EXMON=1 AND TO_CHAR(dtestart, 'd')= 2)) THEN
        intcount   := intcount + 1;
      END IF;
    END LOOP;
    RETURN dtestart;
  END DAYSBETWEEN;
This works for a small set of values for the date, but when there is a larger set of dates, it takes hours to complete due to the while loop and the multiple or statements.

Is there another set of logic I could use to improve this query, possibly not using the while loop/or statements?
This post has been answered by 836082 on Aug 22 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 19 2011
Added on Aug 22 2011
6 comments
903 views