René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

Add working (business) days with Oracle SQL

The following function takes two parameter: p_days and p_dt and tries then to add p_days working days to the p_dt date p_dt. That is, if p_dt points to a Thurdsay, and p_days is 4, it should return the date of the following Wednesday.
I don't know what happens if p_days <=0
create or replace function add_working_days(
  p_days in number, 
  p_dt   in date default trunc(sysdate)
  return date
  v_weeks number; 
  v_adj   number;

  v_weeks := trunc(p_days/5); 

  if to_number(to_char(p_dt,'D')) + mod(p_days,5) >= 7 then 
    v_adj := 2;
    v_adj := 0;
  end if;
  return p_dt + 7*v_weeks + v_adj+mod(p_days,5);
end add_working_days;
Here's a test programm to see what the next 15 working days for May 5th 2004 are:
 a_date date := to_date('05.05.2004','');

  for i in 0 .. 15 loop 
    dbms_output.put_line(to_char(add_working_days(i, a_date), ''));
  end loop;

Thanks a lot to Frank Heijmans who pointed out and corrected an error on this page.