|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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.