Oracle Calculate Working Day

Working Day




create or replace FUNCTION workingday (v_begdate IN DATE, v_enddate IN DATE )
RETURN number IS

currdate date := v_begdate; -- next date

theDay varchar2(10); -- day of the week for currdate

businessday number := 0; -- count for business days

begin

-- start date must be earlier than end date


if v_enddate - v_begdate <= 0 then

return (0);

end if;

loop

-- end_date is reached

exit when currdate = v_enddate;

-- what day of the week is it??????

select to_char(currdate,'fmDay') into theDay from dual;

-- count it only if it is a weekday

if theDay <> 'Saturday' and theDay <> 'Sunday' then

businessday := businessday + 1;

end if;

currdate := to_date(currdate+1);

end loop;

return (businessday);

EXCEPTION

WHEN OTHERS THEN

raise_application_error(-20001,'There was an error in workingday....') ;

return 0;

END workingday ;
/


Function created.



SQL> select workingday ('24-oct-2011','14-nov-2011') "Workingday" from dual;

Workingday
----------
15





select workingday ('24-oct-2011','15-nov-2011') "Workingday" from dual;