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
as
  v_weeks number; 
  v_adj   number;
begin

  v_weeks := trunc(p_days/5); 

  if to_number(to_char(p_dt,'D')) + mod(p_days,5) >= 7 then 
    v_adj := 2;
  else
    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:

declare 
 a_date date := to_date('05.05.2004','dd.mm.yyyy');
begin

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

end;
/

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


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