SQL to return the number of working days between 2 passed in dates

Question

I need to write an sql query that returns the number of Working days (Monday - Friday) between two given dates.

I was wondering what would be the most efficient way to do this?

SELECT           --Start with total number of days including weekends             
(DATEDIFF(dd,@StartDate,@EndDate)+1) --Subtact 2 days for each full weekend 
(DATEDIFF(wk,@StartDate,@EndDate)*2) --If StartDate is a Sunday, Subtract 1          
ELSE 0               END)            --If EndDate is a Saturday, Subtract 1 
FROM dual

Then it would also be helpful to be able to remove holidays from this count such as christmas day and boxing day.

Any Ideas?

Solution 1

Thats so simple :

    SQL> Select count(*)
      2  from ( select rownum rnum
      3          from all_objects
      4        where rownum <= to_date('18-dec-2009','dd-mon-yyyy') - 
    to_date('16-nov-2009')+1 )
      5    where to_char( to_date('16-nov-2009','dd-mon-yyyy')+rnum-1, 'DY' )
      6                not in ( 'SAT', 'SUN' )


      COUNT(*)
    ----------
            25

    SQL> Select to_char( to_date('16-nov-2009','dd-mon-yyyy')+rnum-1, 'DY dd-mon-yyyy' )
      2  from ( select rownum rnum
      3          from all_objects
      4        where rownum <= to_date('18-dec-2009','dd-mon-yyyy') - to_date('16-nov-2009')+1 )
      5    where to_char( to_date('16-nov-2009','dd-mon-yyyy')+rnum-1, 'DY' )
      6                not in ( 'SAT', 'SUN' )


DAY_DATE
---------------
MON 16-nov-2009
TUE 17-nov-2009
WED 18-nov-2009
THU 19-nov-2009
FRI 20-nov-2009
MON 23-nov-2009
TUE 24-nov-2009
WED 25-nov-2009
THU 26-nov-2009
FRI 27-nov-2009
MON 30-nov-2009
TUE 01-dec-2009
WED 02-dec-2009
THU 03-dec-2009
FRI 04-dec-2009
MON 07-dec-2009
TUE 08-dec-2009
WED 09-dec-2009
THU 10-dec-2009
FRI 11-dec-2009
MON 14-dec-2009
TUE 15-dec-2009
WED 16-dec-2009
THU 17-dec-2009
FRI 18-dec-2009

25 rows selected.

Solution 2

an easy way to calculate to number of weekdays between 2 dates is :

SELECT
date1,
date2,
((date2-date1)-2*FLOOR((date2-date1)/7)-DECODE(SIGN(TO_CHAR(date2,'D')-
    TO_CHAR(date1,'D')),-1,2,0)+DECODE(TO_CHAR(date1,'D'),7,1,0)-
    DECODE(TO_CHAR(date2,'D'),7,1,0))*24 as WorkDays
FROM
  tablename
ORDER BY date1,date2

Solution 3

It can be achieved by:

select  SUM(decode ( to_CHAR((sysdate-ROWNUM),'DY'),'SUN',0,'SAT',0,1)) from all_objects where rownum < sysdate -  (sysdate -9) 

Solution 4

Here is an example

with given_days(d) as(
  select <<start_date>> + level - 1
    from dual
  connect by level < = (<<end_date>> - <<start_date>>) + 1
)
select count(*)
  from given_days
where to_char(d, 'DY', 'NLS_DATE_LANGUAGE=english') not in ('SUN', 'SAT')

Demonstration

HR\XE> with given_days as(
  2    select (to_date('&&1', 'dd.mm.yyyy') + level - 1) as g_day
  3      from dual
  4    connect by level < = (to_date('&2', 'dd.mm.yyyy') - to_date('&&1', 'dd.mm.yyyy')) + 1
  5  )
  6  select count(g_day) as cnt
  7    from given_days
  8  where to_char(g_day, 'DY', 'NLS_DATE_LANGUAGE=english') not in ('SUN', 'SAT');
Enter value for 1: 10.10.2012
old   2:   select to_date('&&1', 'dd.mm.yyyy') + level - 1
new   2:   select to_date('10.10.2012', 'dd.mm.yyyy') + level - 1
Enter value for 2: 17.10.2012
old   4:   connect by level < = (to_date('&2', 'dd.mm.yyyy') - to_date('&&1', 'dd.mm.yyyy')) + 1
new   4:   connect by level < = (to_date('17.10.2012', 'dd.mm.yyyy') - to_date('10.10.2012', 'dd.mm.yyyy')) + 1

  cnt                                                                     
----------                           
   6                                                                      

View more solutions

Comments

[gorzan]

To remove holidays, you'll need to join in a holiday calendar that is specific to the locale you want to describe.

[Mac]

Yeh I think I will need to do that, but what about in terms of counting business days? would that work and is it efficient if it is over several years for example?

[nnnn]

go and check Simlier Problem

[Mac]

Needs to be in SQL or PL/SQL nnnn, no C# or anything. Purely Backend

[nnnn]

there is also an answer with a function or SP in databases a stackoverflow.com/a/10930889/507860

[Colin 't Hart]

You use the function DATEDIFF which implies SQL Server, yet you mention that the solution needs to be SQL or PL/SQL implying Oracle. Which is it?

[Mac]

It needs to be SQL or PL/SQL. DateDiff doesnt work. I have realized this haha. Sorry for the confusion. Me being stupid.

[Mac]

Sorry A downvote was not necessary. But it did not work in any way. It could do with more explanation and maybe plugged in values to show how it works. As I do not understand.

[Nick Krasnov]

Is your RDBMS Oracle or SQL Server?

[Nick Krasnov]

Tell me then what exactly you do not understand, what exactly didn't work. What error did your get?

[a_horse_with_no_name]

Be aware that to_char(d, 'DY') will not always return SAT or SUN depending on the NLS settings of the client and the server. You should supply a NLS language to the to_char() function if you want to rely on the text being returned, e.g. to_char(d, 'DY', 'NLS_DATE_LANGUAGE=english')

[NightFury]

Just code is not enough. You should provide some details along with code.

[Jeremy]

This works very well, except that you multiply by 24 at the end to get some kind of work hours concept. Also, this "starts counting" on weekends i.e. saturday->monday is 1 but friday->monday is also 1.

[Scott Robert Schreckengaust]

does not accurately reflect when date1 is greater than date2

[David Balažic]

What if the ALL_OBJECTS table has no enough rows for this trick? ;) A "correct" way to generate a table of enough rows should be used. Also, your example returns 33 for me, not 25.

[David Balažic]

... the reason being NLS, just add 'NLS_DATE_LANGUAGE=english' to each TO_CHAR function call (like in other answers).