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;

Ex:
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;

No comments:

Post a Comment