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
know-oracle.com 
how to write queries in the reports builders
LikeLike
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');
LikeLike
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
LikeLike
–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
LikeLike
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;
LikeLike
your solution is not nls independent
LikeLike