Business days between 2 dates in Oracle

A request I get occasionally is how many business days between 2 arbitrary dates. Oracle is, of course, not at all helpful here. So here is SQL to do it for you (2 methods). The second method relies on 'CONNECT BY' syntax being available so not less than Oracle 10 I'm afraid. The other one is more complex, but should work on any version.

The 2 different methods give slightly different results when the start dates are on Sat/Sun - differing interpretation of when you start counting business days from. The second method is a bit of a hack and only covers 18 months in the past and 18 months in the future. To expand that, change the 500 to the number of days ago you want to start from and the 1000 to the number of days you need to go forward from there. Ridiculously complex I know. but most of it is handling weekend dates.



select
abs( case when to_char(dt2,'d') > 5 then trunc(dt2,'d')+7 else dt2 end -
case when to_char(dt1,'d') > 5 then trunc(dt1,'d')+7 else dt1 end ) -
abs(trunc((trunc( case when to_char(dt2,'d') > 5 then trunc(dt2,'d')+7 else dt2 end ,'d') -
trunc(case when to_char(dt1,'d') > 5 then trunc(dt1,'d')+7 else dt1 end ,'d'))/7)
)*2
method1


, greatest((select count(*) from (select sysdate - 500 + level - 1 dd from dual connect by level< 1000) w where w.dd between dt1 and dt2 and to_char(w.dd,'d') < 6)-1,0) method2

,abs((trunc(dt1,'d')+7) - (trunc(dt2,'d')+7)) - trunc(abs((trunc(dt1,'d')+7) - (trunc(dt2,'d')+7))/7)*2 +
greatest(5 - to_char(dt1,'d'),0) -
greatest(5 - to_char(dt2,'d'),0)
method3


from
(
select to_date('24/08/2011','dd/mm/yyyy') dt1 , to_date('25/8/2011','dd/mm/yyyy') dt2 from dual
union select to_date('25/11/2011','dd/mm/yyyy') dt1 , to_date('28/11/2011','dd/mm/yyyy') dt2 from dual
union select to_date('13/12/2011','dd/mm/yyyy') dt1 , to_date('26/12/2011','dd/mm/yyyy') dt2 from dual
union select to_date('21/12/2011','dd/mm/yyyy') dt1 , to_date('11/01/2012','dd/mm/yyyy') dt2 from dual
union select to_date('22/12/2011','dd/mm/yyyy') dt1 , to_date('13/01/2012','dd/mm/yyyy') dt2 from dual
union select to_date('28/12/2011','dd/mm/yyyy') dt1 , to_date('25/01/2012','dd/mm/yyyy') dt2 from dual
) c
order by c.dt1