Solved

# Oracle - Calculate Business Days

Posted on 2013-11-06
617 Views
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?
0
Question by:patriotpacer
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2

LVL 74

Accepted Solution

sdstuber earned 500 total points
ID: 39628045
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

LVL 32

Expert Comment

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

LVL 74

Expert Comment

ID: 39628099
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 Closing Comment

ID: 39628113
Thanks.  This was what I needed.
0

LVL 32

Expert Comment

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

LVL 32

Expert Comment

ID: 39628174
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 Comment

ID: 39628185
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 Comment

ID: 39628189
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…