Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
How to count days in between 2 dates excluding weekends?

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
Answers
-
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.
-
See
for a way to exclude holidays as well as weekends,
-
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