Oracle PL/SQL – Calculating Weekdays between two dates

Calculating total number of Week Days between two given dates is most common requirement. This article shows few methods to do this.

Calculating total number of Week Days between two given dates

————————————————————-

CREATE OR REPLACE FUNCTION TotWeekDays( FromDate DATE ,

ToDate DATE )

RETURN NUMBER IS

ToTalSunDays NUMBER := NEXT_DAY(ToDate – 7,’SUNDAY’) -

NEXT_DAY(FromDate – 1,’SUNDAY’) ;

ToTalSaturDays NUMBER := NEXT_DAY(ToDate – 7,’SATURDAY’) -

NEXT_DAY(FromDate – 1,’SATURDAY’) ;

BEGIN

RETURN (ToDate – FromDate – (TotalSundays+TotalSaturdays)/7 -1) ;

END ;

Executing this function in a pl/sql block…

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

2 BusinessDays NUMBER;

3 BEGIN

4 BusinessDays := TotWeekDays(’01-MAY-2001′,’31-MAY-2001′) ;

5 DBMS_OUTPUT.PUT_LINE(‘Total Business Days : ‘||TO_CHAR(BusinessDays) ) ;

6* END;

SQL> /

Total Business Days : 23