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?
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;