Forum Stats

  • 3,826,110 Users
  • 2,260,598 Discussions
  • 7,896,789 Comments

Discussions

How to count days in between 2 dates excluding weekends?

User_R6EDA
User_R6EDA Member Posts: 1 Red Ribbon
edited May 17, 2022 4:21AM in SQL & PL/SQL

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

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,047 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 42,047 Red Diamond
  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond

    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