How to get business days between two dates?

[gsreenathreddy]

Hi!
There are two given dates and I have to find out the working days between two dates i.e. excluding Sundays. Please help. Thank you.

[amitpatel66]

Hi!
There are two given dates and I have to find out the working days between two dates i.e. excluding Sundays. Please help. Thank you.

Try this:

 
SELECT dt FROM
(SELECT TO_DATE(:start_date,'DD-MON-YYYY') + level - 1 dt FROM table_name CONNECT BY level <= TO_DATE(:end_date,'DD-MON-YYYY') - TO_DATE(:start_date,'DD-MON-YYYY')) WHERE TO_DATE(dt,'Day') != 'Sunday';
 
 

PS: Not Tested!!

[subashsavji]

Hi!
There are two given dates and I have to find out the working days between two dates i.e. excluding Sundays. Please help. Thank you.

MODIFIED OF AMITH PETELS CODE

 
SELECT HIREDATE FROM 
(SELECT TO_DATE(&start_date,'DD-MON-YYYY') + level - 1 HIREDATE FROM EMP
 CONNECT BY level <= TO_DATE(&end_date,'DD-MON-YYYY') - TO_DATE(&start_date,'DD-MON-YYYY') ) 
WHERE TO_DATE(HIREDATE,'Day') != 'Sunday'
/
 

[subashsavji]

Try this:

 
SELECT dt FROM
(SELECT TO_DATE(:start_date,'DD-MON-YYYY) + level - 1 dt FROM table_name CONNECT BY level <= TO_DATE(:end_date,'DD-MON-YYYY) - TO_DATE(:start_date,'DD-MON-YYYY')) WHERE TO_DATE(dt,'Day') != 'Sunday';
 
 

PS: Not Tested!!

 
WITH date_tab AS
         (SELECT       TO_DATE ('&from_date', 'dd-MON-yyyy')
                     + LEVEL
                     - 1 business_date
                FROM DUAL
          CONNECT BY LEVEL <=
                          TO_DATE ('&to_date', 'dd-MON-yyyy')
                        - TO_DATE ('&from_date', 'dd-MON-yyyy')
                        + 1)
  SELECT business_date
    FROM date_tab
   WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');
//
 

 
CREATE OR REPLACE FUNCTION F_BUSINESS_DAYS
(V_START_DATE IN DATE, V_END_DATE IN DATE)
RETURN NUMBER IS DAY_COUNT NUMBER := 0;
CURR_DATE DATE;
BEGIN -- loop through and update
CURR_DATE := V_START_DATE;
WHILE CURR_DATE <= V_END_DATE
LOOP
IF TO_CHAR(CURR_DATE,'DY') NOT IN ('SAT','SUN')
THEN DAY_COUNT := DAY_COUNT + 1;
END IF;
CURR_DATE := CURR_DATE + 1;
END LOOP;
RETURN DAY_COUNT;
END F_BUSINESS_DAYS;
//
 
 

[Sandeep Gaadhe]

Dear Subhashsavji,

The solution (WITH date_tab AS ) works fine...great query.

Many Thanks.

[debasisdas]

Also check this for more customized version.