Calculating Weekdays between two dates
CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)
RETURN NUMBER IS
totalsundays NUMBER;
totalsaturdays NUMBER;
begin
totalsundays
:= NEXT_DAY (todate – 7, ‘sunday’)
– NEXT_DAY (fromdate – 1, ‘sunday’);
totalsaturdays
:= NEXT_DAY (todate – 7, ‘saturday’)
– NEXT_DAY (fromdate – 1, ‘saturday’);
RETURN (todate – fromdate – (totalsundays + totalsaturdays) / 7 – 1);
END totworkdays;
Call this function as follows:
declare
lv_tot_work_days number;
begin
lv_tot_work_days := totworkdays (’01-jan-2009′, ’31-jan-2009′);
dbms_output.put_line(‘Total Work Days: ‘||lv_tot_work_days);
end;
Leave a Reply
Want to join the discussion?Feel free to contribute!