469,301 Members | 2,099 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

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 30505
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
  5.                      - 1 business_date
  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)
  11.   SELECT business_date
  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;
  16. END F_BUSINESS_DAYS;
  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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by David Stockwell | last post: by
12 posts views Thread by Anthony Robinson | last post: by
1 post views Thread by igendreau | last post: by
7 posts views Thread by Sam | last post: by
2 posts views Thread by brains4math | last post: by
8 posts views Thread by =?Utf-8?B?QWw=?= | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.