472,330 Members | 1,215 Online

# How to get business days between two dates?

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.
Feb 6 '08 #1
5 31365
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
1.
2. SELECT dt FROM
3. (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';
4.
5.
PS: Not Tested!!
Feb 6 '08 #2
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
Expand|Select|Wrap|Line Numbers
1.
2. SELECT HIREDATE FROM
3. (SELECT TO_DATE(&start_date,'DD-MON-YYYY') + level - 1 HIREDATE FROM EMP
4.  CONNECT BY level <= TO_DATE(&end_date,'DD-MON-YYYY') - TO_DATE(&start_date,'DD-MON-YYYY') )
5. WHERE TO_DATE(HIREDATE,'Day') != 'Sunday'
6. /
7.
Feb 7 '08 #3
Try this:

Expand|Select|Wrap|Line Numbers
1.
2. SELECT dt FROM
3. (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';
4.
5.
PS: Not Tested!!
Expand|Select|Wrap|Line Numbers
1.
2. WITH date_tab AS
3.          (SELECT       TO_DATE ('&from_date', 'dd-MON-yyyy')
4.                      + LEVEL
6.                 FROM DUAL
7.           CONNECT BY LEVEL <=
8.                           TO_DATE ('&to_date', 'dd-MON-yyyy')
9.                         - TO_DATE ('&from_date', 'dd-MON-yyyy')
10.                         + 1)
12.     FROM date_tab
13.    WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');
14. //
15.

Expand|Select|Wrap|Line Numbers
1.
2. CREATE OR REPLACE FUNCTION F_BUSINESS_DAYS
3. (V_START_DATE IN DATE, V_END_DATE IN DATE)
4. RETURN NUMBER IS DAY_COUNT NUMBER := 0;
5. CURR_DATE DATE;
6. BEGIN -- loop through and update
7. CURR_DATE := V_START_DATE;
8. WHILE CURR_DATE <= V_END_DATE
9. LOOP
10. IF TO_CHAR(CURR_DATE,'DY') NOT IN ('SAT','SUN')
11. THEN DAY_COUNT := DAY_COUNT + 1;
12. END IF;
13. CURR_DATE := CURR_DATE + 1;
14. END LOOP;
15. RETURN DAY_COUNT;
17. //
18.
19.
Feb 7 '08 #4
Dear Subhashsavji,

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

Many Thanks.
Oct 15 '10 #5
debasisdas
8,127 Expert 4TB
Also check this for more customized version.
Oct 18 '10 #6