---

Oracle / PLSQL: Add days to a date (skipping Saturdays and Sundays)

Question: I have managed to add days to a given date in a procedure. My question is how do I manipulate the procedure to skip weekends and holidays?

For example:

If the date is 19/9/2003 and I add 2 days, the resulting date should be 23/9/2003.

Answer: To skip weekends when adding days to a date, you will need to create a custom function. Below is a function that we've written called custom_add_days. It accepts two parameters - start_date_in and days_in.

This function takes the start_date_in value and adds the number of days in the days_in variable, skipping Saturdays and Sundays.

This function does not skip holidays as Oracle has no way of recognizing which days are holidays. However, you could try populating a holiday table and then query this table to determine additional days to skip.

CREATE OR REPLACE Function custom_add_days
   (start_date_in date, days_in number)
   return date
IS
   v_counter number;
   v_new_date date;
   v_day_number number;

BEGIN

   /* This routine will add a specified number of days (ie: days_in) to a date (ie: start_date). */
   /* It will skip all weekend days - Saturdays and Sundays */
   v_counter := 1;
   v_new_date := start_date_in;

   /* Loop to determine how many days to add */
   while v_counter <= days_in
   loop

      /* Add a day */
      v_new_date := v_new_date + 1;
      v_day_number := TO_CHAR(v_new_date, 'd');

      /* Increment counter if day falls between Monday to Friday */
      if v_day_number >= 2 and v_day_number <= 6 then
         v_counter := v_counter + 1;
      end if;

   end loop;

   RETURN v_new_date;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;