How to get business days between two dates?
August 28th, 2006 By madan30
There are two given dates and I have to find out the working days between two dates i.e. excluding Sundays and Saturdays. Please help. Thank you.
One Response to “How to get business days between two dates?”
Leave a Reply
You must be logged in to post a comment.
August 28th, 2006 at 11:14 am
Here is one way:
EDDIE@XE> SELECT mydate 2 FROM (SELECT TO_DATE ('&&from_date', 'mm-dd-yyyy') + ROWNUM - 1 mydate 3 FROM all_objects 4 WHERE ROWNUM < = 5 TO_DATE ('&&to_date', 'mm-dd-yyyy') 6 - TO_DATE ('&&from_date', 'mm-dd-yyyy') 7 + 1) 8 WHERE TO_CHAR (mydate, 'DY') NOT IN ('SAT', 'SUN') 9 / Enter value for from_date: 8-28-2006 Enter value for to_date: 9-4-2006 MYDATE --------- 28-AUG-06 29-AUG-06 30-AUG-06 31-AUG-06 01-SEP-06 04-SEP-06 6 rows selected.Or, if you have 9i DB and above:
EDDIE@XE> SELECT mydate 2 FROM (SELECT TO_DATE ('&&from_date', 'mm-dd-yyyy') + LEVEL - 1 mydate 3 FROM DUAL 4 CONNECT BY LEVEL < = 5 TO_DATE ('&&to_date', 'mm-dd-yyyy') 6 - TO_DATE ('&&from_date', 'mm-dd-yyyy') 7 + 1) 8 WHERE TO_CHAR (mydate, 'DY') NOT IN ('SAT', 'SUN') 9 / MYDATE --------- 28-AUG-06 29-AUG-06 30-AUG-06 31-AUG-06 01-SEP-06 04-SEP-06 6 rows selected.Related AskTom threads:
Counting the number of business days between 2 dates
Retrieve Dates Between Two Dates