PLSQL Stored Procedure to calculate number of particular day between two input dates.

CREATE OR REPLACE PROCEDURE PR_TOT_DAYS
(
P_START_DATE IN DATE,
P_END_DATE IN DATE,
P_DAY IN VARCHAR2,
P_TOT_DAYS OUT NUMBER
)
IS
LV_LOOP NUMBER;
LV_DATE DATE;
LV_TOT_DAYS NUMBER := 0;

BEGIN

LV_LOOP := (P_END_DATE – P_START_DATE)+1 ;
FOR I IN 1..LV_LOOP
LOOP
IF I = 1 THEN
LV_DATE := P_START_DATE;
ELSIF I > 1 THEN
LV_DATE := P_START_DATE + I;
END IF;

IF TRIM(TO_CHAR(LV_DATE,’DAY’)) = UPPER(P_DAY) THEN
LV_TOT_DAYS := LV_TOT_DAYS +1;
END IF;

LV_DATE := P_START_DATE;

END LOOP;
P_TOT_DAYS := LV_TOT_DAYS ;

END;

Calling the Above Procedure and printing the output,

DECLARE
P_START_DATE DATE;
P_END_DATE DATE;
P_DAY VARCHAR2(20);
P_TOT_DAYS NUMBER(3);
BEGIN
P_START_DATE := TO_DATE(’01-05-2021′,’DD-MM-YYYY’);
P_END_DATE := TO_DATE(’31-10-2021′,’DD-MM-YYYY’);
P_DAY := ‘MONDAY’;
PR_TOT_DAYS(P_START_DATE,P_END_DATE,P_DAY,P_TOT_DAYS);

DBMS_OUTPUT.PUT_LINE(‘Total Number of ‘||P_DAY||’ between ‘||P_START_DATE||’ and ‘||P_END_DATE||’ is ‘||PR_TOT_DAYS);

END;