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

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor 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
 
awking00Commented:
How would you want to treat the case where today is Saturday or Sunday?
0
 
sdstuberCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
patriotpacerAuthor Commented:
Thanks.  This was what I needed.
0
 
awking00Commented:
Assume today was Sunday, how many business days are left in the week, 0 or 5?
0
 
awking00Commented:
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
 
patriotpacerAuthor 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
 
patriotpacerAuthor Commented:
0
All Courses

From novice to tech pro — start learning today.