Oracle SQl Dev, how to count the number of weekdays between 2 dates

Question

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

Answer 1

This answer is similar to Nicholas, which is not surprising, because you need a subquery with CONNECT BY to cross out the list of dates. Then the dates can be recounted by checking the day of the week. The difference here is that it shows how to get the value of the day of the week count in each row of 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 invoice is turned on, i.e. includes FromDate and ThruDate . This query assumes that your dates do not have a time component; if they do, you will need the TRUNC date columns in the subquery.

Answer 2

You can do it as follows:

Suppose we want to know how many workdays 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 the date data type, the TO_DATE() function is not 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 

Answer 3

Checkout my full working function code and explain https://sqljana.wordpress.com/2017/03/16/oracle-calculating-business-days-between-two-dates-in-oracle/

Once you have created the function, simply use this function as part of the SELECT statement and go to the two date columns for the start and end dates, for example:

SELECT Begin_Date, End_Date, fn_GetBusinessDaysInterval (Begin_Date, End_Date) AS BusinessDays FROM YOURTABLE;