Oracle SQl Dev, how to calc num of weekdays between 2 dates

Question

Does anyone know how can I calculate the number of weekdays between two date fields? I'm using oracle sql developer. I need to find the average of weekdays between multiple start and end dates. So I need to get the count of days for each record so I can average them out. Is this something that can be done as one line in the SELECT part of my query?

Answer 1

This answer is similar to Nicholas's, which isn't a surprise because you need a subquery with a CONNECT BY to spin out a list of dates. The dates can then be counted while checking for the day of the week. The difference here is that it shows how to get the weekday count value on each line of the results:

SELECT
  FromDate,
  ThruDate,
  (SELECT COUNT(*)
     FROM DUAL
     WHERE TO_CHAR(FromDate + LEVEL - 1, 'DY') NOT IN ('SAT', 'SUN')
     CONNECT BY LEVEL <= ThruDate - FromDate + 1
  ) AS Weekday_Count
FROM myTable

The count is inclusive, meaning it includes FromDate and ThruDate. This query assumes that your dates don't have a time component; if they do you'll need to TRUNC the date columns in the subquery.

Answer 2

You could do it the following way :

Lets say we want to know how many weekdays between start_date='01.08.2013' and end_date='04.08.2013' In this example start_date and end_date are string literals. If your start_date and end_date are of date datatype, the TO_DATE() function won't be needed:

select count(*) as num_of_weekdays
  from ( select level as dnum
           from dual
        connect by (to_date(end_date, 'dd.mm.yyyy') - 
                    to_date(start_date, 'dd.mm.yyyy') + 1) - level >= 0) s
where to_char(sysdate + dnum, 'DY', 
              'NLS_DATE_LANGUAGE=AMERICAN') not in ('SUN', 'SAT')

Result:

num_of_weekdays
--------------
2