# 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.
Asked:
###### Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x

Commented:
Assuming you mean days that aren't Saturday or Sunday and you don't want to include the current day, then try something like this...

change

TO_DATE('2013-11-09', 'yyyy-mm-dd')

to whatever end-point you're interested in

SELECT COUNT(*)
FROM (    SELECT TRUNC(SYSDATE) + LEVEL  d
FROM DUAL
CONNECT BY TRUNC(SYSDATE) + LEVEL <= TO_DATE('2013-11-09', 'yyyy-mm-dd'))
WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun')

Since today is 11/6,  this generates the days 11/7, 11/8, 11/9  in the inner query  (tomorrow through end of period)

then throws out 11/9 because it's Saturday and counts what is left over
0

Commented:
How would you want to treat the case where today is Saturday or Sunday?
0

Commented:
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
0

Author Commented:
Thanks.  This was what I needed.
0

Commented:
Assume today was Sunday, how many business days are left in the week, 0 or 5?
0

Commented:
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;
0

Author Commented:
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.
0

Author Commented:
0

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.