patriotpacer
asked on
Oracle - Calculate Business Days
Is there a way to calculate dates with only business days?
For example, based on today's date, how many business days are left for the week?
Answer would be 2.
For example, based on today's date, how many business days are left for the week?
Answer would be 2.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
awking00
How would you want to treat the case where today is Saturday or Sunday?
the query above would already handle that. Assuming, of course, that Saturday and Sunday aren't considered business days.
Here's a slightly more versatile version of the above. Simply change the start_date and end_date values to whatever you want.
If you want to use "today" as the start_date or end_date, then use TRUNC(SYSDATE) as shown above in the original query.
SELECT COUNT(*)
FROM ( SELECT start_date + LEVEL d
FROM (SELECT DATE '2013-11-02' start_date, DATE '2013-11-09' end_date FROM DUAL)
CONNECT BY start_date + LEVEL <= end_date)
WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun');
This example returns 5 business days between last Saturday and this coming Saturday
Here's a slightly more versatile version of the above. Simply change the start_date and end_date values to whatever you want.
If you want to use "today" as the start_date or end_date, then use TRUNC(SYSDATE) as shown above in the original query.
SELECT COUNT(*)
FROM ( SELECT start_date + LEVEL d
FROM (SELECT DATE '2013-11-02' start_date, DATE '2013-11-09' end_date FROM DUAL)
CONNECT BY start_date + LEVEL <= end_date)
WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun');
This example returns 5 business days between last Saturday and this coming Saturday
ASKER
Thanks. This was what I needed.
Assume today was Sunday, how many business days are left in the week, 0 or 5?
If the query is never going to be run on Saturday or Sunday, I kind of like the following for simplicity:
select 6 - to_char(sysdate,'d') busdaysleft from dual;
select 6 - to_char(sysdate,'d') busdaysleft from dual;
ASKER
awking00 - Thank you for your post. Unfortunately, the query will need to run every day.
BTW - I'm about to post a follow up to this question.
BTW - I'm about to post a follow up to this question.