How to count days in between 2 dates excluding weekends?

User_R6EDA

Example:

In table Orders I have fields approved_date and creation_date. I want to count how many days between both dates.

approved_date | creation_date | approved - creation

05/16/2022 | 05/12/2022 | 4

Expected Output excluding Saturday and Sunday.:

approved_date | creation_date | approved - creation

05/16/2022 | 05/12/2022 | 2

Frank Kulash

Hi, @User_R6EDA

Welcome to the forum!

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

Here's one way to do what you requested:

SELECT   t.approved_date, t.creation_date
,	 c.num_weekdays
FROM	 table_x t
CROSS APPLY (
   	        SELECT  COUNT (*) AS num_weekdays
		FROM	dual
		WHERE   TO_CHAR ( t.creation_date + LEVEL
				, 'DY'
				, 'NLS_DATE_LANGUAGE=ENGLISH'
				) NOT IN ('SAT', 'SUN') 
		CONNECT BY LEVEL <= approved_date - creation_date
   	  )   c
ORDER BY  t.approved_date, t.creation_date	-- or whatever you want
;

This assumes creation_date is no later than approved_date. Creation_date is not included in the results, but approved_date is included.

Frank Kulash

See

https://community.oracle.com/tech/developers/discussion/3974709/business-hours-and-no-weekend

for a way to exclude holidays as well as weekends,

mathguy

Here's a direct computation in closed form that achieves the same result. It sees how many weekdays passed from "Monday" till the approved date, resp. creation date, and adjusts for these counts. Then it considers the difference between those two respective Mondays and computes 5/7 times the difference in days.

This can be modified to account for holidays also - counting how many holidays, from a separate table showing the holidays, are not Sat/Sun and fall between the creation date and the approval date.

This approach may be preferred over "specific enumeration" (as demonstrated in other answers) especially if you must perform the same computation for a very large number of rows.

The solution below assumes the dates are "pure dates" (the time-of-day is 00:00:00), and that the creation date is counted but the approval date is not counted. All these assumptions can be relaxed, with minor modifications to the code.

with
  orders (approved_date, creation_date) as (
    select date '2022-05-16', date '2022-05-12' from dual union all
    select date '2022-03-13', date '2020-08-21' from dual
  )   -- for testing only (not required in the real-case problem)
select approved_date, creation_date,
         least(5, approved_date - trunc(approved_date, 'iw') + 1)
       - least(5, creation_date - trunc(creation_date, 'iw') + 1)
       + (trunc(approved_date, 'iw') - trunc(creation_date, 'iw')) * 5 / 7
       as weekday_count
from   orders
;

APPROVED_DATE  CREATION_DATE  WEEKDAY_COUNT
-------------  -------------  -------------
05/16/2022     05/12/2022                 2
03/13/2022     08/21/2020               405