Re: Working days

> Kenneth, many thanks. I didn't give the full story in my original
> posting :(
>
> I want to add a variable number of days, not always 3. Ie I want to
> add 3 days, 6 days, 7 days, 9 days etc - it will be different every
> time. I was wanting it generic where I could just put the number of
> days into the SQL string & spit out the result. Your solution is
> perfect for 3 days, which is what I asked for, but not for other
> numbers of days.
>
> Can someone elaborate on how to make it generic.
>
> Help
> Monty.
 

Try out this function:

• don't know what happens if p_days <=0
• or when p_dt is a saturday or sunday. 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_char(p_dt + mod(p_days,5),'D') = '1' then     v_adj := 1;
  elsif to_char(p_dt + mod(p_days,5),'D') = '7' then     v_adj := 2;
  else
    if to_char(p_dt + mod(p_days,5) ,'D') < to_char(p_dt,'D') then       v_adj := 2;
    else
      v_adj := 0;
    end if;
  end if;

  return p_dt + 7*v_weeks + v_adj+mod(p_days,5); end add_working_days;
/

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html