How to get working days between two dates in Oracle PL-SQL

How to get working days in a year

Following function returns the working days(excluding Saturdays,Sundays) between two dates.

CREATE OR REPLACE FUNCTION WORKING_DAYS (START_DATE IN DATE, END_DATE IN DATE)

   RETURN NUMBER

IS

   RETVAL           NUMBER (15, 7);

   NEW_START_DATE   DATE;

   NEW_END_DATE     DATE;

   BDAYSTART        NUMBER (15, 15);

   BDAYEND          NUMBER (15, 15);

BEGIN

   NEW_START_DATE := START_DATE;

   NEW_END_DATE := END_DATE;

-- SET DEFAULTS FOR BUSINESS DAY START AND END. CAN BE OVERRIDDEN PER REGION

   BDAYSTART := 7 / 24;

   BDAYEND := 17 / 24;

   NEW_START_DATE := NEW_START_DATE + 15 / 24;

   NEW_END_DATE := NEW_END_DATE + 15 / 24;

--START AFTER END OF DAY, MAKE START BE START OF NEXT DAY

   IF NEW_START_DATE - TRUNC (NEW_START_DATE) > BDAYEND

   THEN

      NEW_START_DATE := TRUNC (NEW_START_DATE + 1) + BDAYSTART;

   END IF;

--START BEFORE START OF DAY, MAKE START BE START OF SAME DAY

   IF NEW_START_DATE - TRUNC (NEW_START_DATE) < BDAYSTART

   THEN

      NEW_START_DATE := TRUNC (NEW_START_DATE) + BDAYSTART;

   END IF;

--START SATURDAY, MAKE START BE MONDAY START OF DAY

   IF TO_CHAR (NEW_START_DATE, 'D') = 7

   THEN

      NEW_START_DATE := TRUNC (NEW_START_DATE + 2) + BDAYSTART;

   END IF;

--START SUNDAY, MAKE START BE MONDAY START OF DAY

   IF TO_CHAR (NEW_START_DATE, 'D') = 1

   THEN

      NEW_START_DATE := TRUNC (NEW_START_DATE + 1) + BDAYSTART;

   END IF;

-- END AFTER END OF DAY, MAKE END BE END OF DAY SAME DAY

   IF NEW_END_DATE - TRUNC (NEW_END_DATE) > BDAYEND

   THEN

      NEW_END_DATE := TRUNC (NEW_END_DATE) + BDAYEND;

   END IF;

-- END BEFORE START OF DAY, MAKE END BE START OF DAY THE SAME DAY

   IF NEW_END_DATE - TRUNC (NEW_END_DATE) < BDAYSTART

   THEN

      NEW_END_DATE := TRUNC (NEW_END_DATE) + BDAYSTART;

   END IF;

--END ON SATURDAY, MAKE IT BE THE END OF THE DAY ON FRIDAY

   IF TO_CHAR (NEW_END_DATE, 'D') = 7

   THEN

      NEW_END_DATE := TRUNC (NEW_END_DATE - 1) + BDAYEND;

   END IF;

--END ON SUNDAY, MAKE IT BE THE END OF THE DAY ON FRIDAY

   IF TO_CHAR (NEW_END_DATE, 'D') = 1

   THEN

      NEW_END_DATE := TRUNC (NEW_END_DATE - 2) + BDAYEND;

   END IF;

--FACTOR OUT WEEKEND DAYS

   RETVAL :=

        NEW_END_DATE

      - NEW_START_DATE

      - ((TRUNC (NEW_END_DATE, 'D') - TRUNC (NEW_START_DATE, 'D')) / 7) * 2;

-- IF HOLIDAYS WERE TO BE CALCULATED, THE CALCULATION WOULD GO HERE

-- IF END IS DURING NON BUSINESS HOURS, DIFFERENCE COULD BE NEGATIVE

   IF RETVAL < 0

   THEN

      RETVAL := 0;

   END IF;

   RETURN (RETVAL);

END;

Leave a comment for any query.