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

Advertisements

8 thoughts on “Oracle PL/SQL – Calculating Weekdays between two dates

  1. WITH x AS ( SELECT TO_CHAR (SYSDATE + 100 – LEVEL + 1, ‘DAY’) daze
    FROM DUAL
    CONNECT BY LEVEL <= SYSDATE + 100 – SYSDATE)
    SELECT COUNT (daze)
    FROM x
    WHERE UPPER (TRIM (daze)) NOT IN ('SATURDAY', 'SUNDAY');

    Like

  2. Dear Author,

    Please advise me how can i apply the same program for my application , as for us the holidays in a week follows for thursday half session and friday . we are working on saturday and sunday.

    I need the logic of the above program to suit my application needs, as i have one invoice calculation based on weeks , which is going wrong for many days and it is effecting accounts.

    Thanks and regards
    Sunil Dutt.S

    Like

  3. –Perfect function to get Next Business Date excluding holidays and Weekends

    –Lets say you have a HOLIDAY_MASTER table
    CREATE TABLE HOLIDAY_MASTER
    ( HOLIDAY_ID NUMBER(9) PRIMARY KEY,
    HOLIDAY_GROUP_ID NUMBER(9),
    HOLIDAY_DATE DATE
    )

    –Lets insert some records
    Insert into HOLIDAY_MASTER (HOLIDAY_ID,HOLIDAY_GROUP_ID,HOLIDAY_DATE)
    values (1,1,to_date(’01-JAN-2010′,’DD-MON-YYYY’));
    Insert into HOLIDAY_MASTER (HOLIDAY_ID,HOLIDAY_GROUP_ID,HOLIDAY_DATE)
    values (2,1,to_date(’04-JAN-2010′,’DD-MON-YYYY’));
    Insert into HOLIDAY_MASTER (HOLIDAY_ID,HOLIDAY_GROUP_ID,HOLIDAY_DATE)
    values (3,1,to_date(’06-JAN-2010′,’DD-MON-YYYY’));
    COMMIT;

    –Create function to get next business date
    CREATE OR REPLACE FUNCTION GET_NEXT_BNS_DATE(V_DATE IN DATE, V_HOL_GRP_ID IN INTEGER, V_NO_OF_DAYS IN INTEGER)
    RETURN DATE IS
    V_NEXT_DATE DATE;
    TYPE v_holiday_type IS TABLE OF DATE INDEX BY BINARY_integer;
    v_holiday v_holiday_type;
    V_holiday_count NUMBER;
    BEGIN
    V_NEXT_DATE:=V_DATE;

    FOR x in 1 .. V_NO_OF_DAYS LOOP
    V_NEXT_DATE:=V_NEXT_DATE 1;
    SELECT COUNT(*) INTO v_holiday_count FROM HOLIDAY_MASTER WHERE HOLIDAY_GROUP_ID=V_HOL_GRP_ID;

    IF (v_holiday_count > 0) THEN
    SELECT holiday_date bulk collect into v_holiday
    FROM HOLIDAY_MASTER WHERE HOLIDAY_GROUP_ID=V_HOL_GRP_ID
    ORDER BY holiday_date;

    FOR i in 1..v_holiday.last loop
    IF V_NEXT_DATE IN (v_holiday(i)) THEN
    V_NEXT_DATE:=v_holiday(i) 1;
    IF (TO_CHAR(V_NEXT_DATE, ‘DY’)=’SAT’) THEN
    V_NEXT_DATE:=V_NEXT_DATE 2;
    ELSIF (TO_CHAR(V_NEXT_DATE, ‘DY’)=’SUN’) THEN
    V_NEXT_DATE:=V_NEXT_DATE 1;
    END IF;
    END IF;
    END LOOP;
    END IF;

    IF (TO_CHAR(V_NEXT_DATE, ‘DY’)=’SAT’) THEN
    V_NEXT_DATE:=V_NEXT_DATE 2;
    ELSIF (TO_CHAR(V_NEXT_DATE, ‘DY’)=’SUN’) THEN
    V_NEXT_DATE:=V_NEXT_DATE 1;
    END IF;

    END LOOP;

    RETURN V_NEXT_DATE;

    END GET_NEXT_BNS_DATE;
    /

    –To use this function
    SELECT GET_NEXT_BNS_DATE(TO_DATE(’31-DEC-2009′,’DD-MON-YYYY’),1,3) FROM DUAL;

    –The output of above will be 08-JAN-2010, according to the given example of HOLIDAY_MASTER table

    Like

  4. Thanks for posting this. My variation that seems to work:

    FUNCTION ttlWeekdaysinTheMonth(vTheDate in DATE) RETURN NUMBER IS
    /*
    Return # of Weekdays in Month of specified Date
    Weekdays = Mon – Fri
    In Oracle/PLSQL, the next_day function returns the first weekday that is greater than a date.
    modified from
    https://knoworacle.wordpress.com/2009/05/13/oracle-plsql-%E2%80%93-calculating-weekdays-between-two-dates/
    */

    FirstDayofMonth DATE := CONCAT(’01’, SUBSTR(TO_CHAR(vTheDate),3,9));

    TotalSundays NUMBER := – trunc( (NEXT_DAY(FirstDayofMonth -1,’SUNDAY’) – NEXT_DAY(last_day(vTheDate),’SUNDAY’) ) / 7) ;

    TotalSaturdays NUMBER := – trunc((NEXT_DAY(FirstDayofMonth-1 ,’SATURDAY’) – NEXT_DAY(last_day(vTheDate) ,’SATURDAY’) ) / 7 ) ;

    TotalAllDays NUMBER := trunc(last_day(vTheDate) – FirstDayofMonth + 1);

    BEGIN

    RETURN (TotalAllDays – TotalSundays – TotalSaturdays) ;

    END ttlWeekdaysinTheMonth;

    Like

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s