Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 641
  • Last Modified:

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.
0
patriotpacer
Asked:
patriotpacer
  • 3
  • 3
  • 2
1 Solution
 
sdstuberCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now