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

count, date, oracle, sql

Solution

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.

Problem

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?

Original source