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?

Answer: 
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

```
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                                                                      

```