PL/SQL - Calculate Number of Business Days in a Given Month

if you need a function for this: this one counts the working days (mo- fr) for a given date. the calculating is based on

SELECT TO_CHAR(SYSDATE, 'D') FROM dual;

if this returns 2 for monday (6 for friday), it will work (depending on your NLS settings this may vary), otherwise you have to change the code.

 CREATE OR REPLACE FUNCTION get_working_days (
     p_start_date IN DATE
     )
 RETURN NUMBER IS
     v_last_day NUMBER;
     v_cnt NUMBER := 0;
 BEGIN
     SELECT TO_CHAR(LAST_DAY(p_start_date), 'DD')
     INTO   v_last_day
     FROM   dual;
     --
     FOR i IN 1..v_last_day
     LOOP
         IF TO_CHAR(p_start_date - 1 + i, 'D') BETWEEN 2 AND 6 THEN
            v_cnt := v_cnt + 1;
         END IF;
     END LOOP;
     --
     RETURN(v_cnt);
     --
 END;

 

What is the source for your "given" month (this will obviously need to include the year as well)? If it is a date field, then jwahl's function will get you what you need although I think it may need a modification to make sure that the date parameter passed in is adjusted to the first day of the month. This can be done changing this
IF TO_CHAR(p_start_date - 1 + i, 'D') ...
to this -
IF TO_CHAR(trunc(p_start_date,'month') - 1 + i, 'D') ...
If the source is something other than a date field, we can create a function to deal with that as well. For example, if it is character data in the form of 'yyyymm', the following function will work:
create or replace function get_bus_days(yr_mth_in in varchar2) return number is
beg_day   date;
end_day   date;
tot_days  pls_integer;
bus_days  pls_integer := 0;
day_of_wk pls_integer;

begin
beg_day := to_date(yr_mth_in,'yyyymm');
end_day := add_months(beg_day,1) - 1;
tot_days := end_day - beg_day;
for i in 0..tot_days
loop
day_of_wk := to_number(to_char(beg_day + i,'D'));
if day_of_wk between 2 and 6 then
bus_days := bus_days + 1;
end if;
end loop;
return bus_days;
end;
/

Here's an adaptation from jwahl's code. It can be easily adapted to a function.

Some holidays are taken into consideration - there are two kinds: fixed date (Independence Day) or variable date (Thanksgiving). You can add your own.

declare      
   startDate date := sysdate;
   ctrDate date ;  
   numMonth number;
   numDay number;
   dayCount number := 0;
 BEGIN
     dbms_output.enable;          
     ctrDate := trunc(startDate, 'MM');          
     
     -- calculate business days in the month
     while (ctrDate <= last_day(startDate))
     loop
         if to_char(ctrDate, 'D') between 2 and 6 then
            dayCount := dayCount + 1;
         end if;
         ctrDate := ctrDate+1;              
     end loop;
     
     numMonth := to_number(to_char(startDate, 'MM'));
     numDay := to_number(to_char(startDate, 'DD'));
     
     -- eliminate 1/1, 7/4, 12/25
     if ((numMonth = 1) and to_number(to_char(to_date('01/01', 'MM/DD'), 'MM')) between 2 and 6 ) or
        ((numMonth = 7) and to_number(to_char(to_date('07/04', 'MM/DD'), 'MM')) between 2 and 6 ) or
        ((numMonth = 12) and to_number(to_char(to_date('12/25', 'MM/DD'), 'MM')) between 2 and 6 )
       then dayCount := dayCount-1;
     end if;
     
     
     -- eliminate variable date public holidays that fall on working days
     -- MLK = 3rd Monday in January
     -- Memorial = last Monday in May
     -- Labor = first Monday in September
     -- Thanksgiving = 4th Thursday in November    
     if numMonth in (1,5,9,11) then
       dayCount := dayCount-1;
     end if;
     
     dbms_output.put_line('#working days in this month: '||dayCount);
 END;