Oracle: Days between two date and Exclude weekdays how to handle negative numbers

by

Adapted from my answer here:

Get the number of days between the Mondays of both weeks (using TRUNC( datevalue, 'IW' ) as an NLS_LANGUAGE independent method of finding the Monday of the week) then add the day of the week (Monday = 1, Tuesday = 2, etc., to a maximum of 5 to ignore weekends) for the end date and subtract the day of the week for the start date. Like this:

SELECT ( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * 5 / 7
       + LEAST( end_date - TRUNC( end_date, 'IW' ) + 1, 5 )
       - LEAST( start_date - TRUNC( start_date, 'IW' ) + 1, 5 )
          AS WeekDaysDifference
FROM   your_table

More Related Contents:

  1. Calculate difference between 2 date / times in Oracle SQL
  2. Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  3. How to generate entire DDL of an Oracle schema (scriptable)?
  4. How to get records randomly from the oracle database?
  5. Oracle PL/SQL – Raise User-Defined Exception With Custom SQLERRM
  6. Auto Increment for Oracle
  7. Get resultset from oracle stored procedure
  8. Deploying and Configuring ODP.NET to work without installation with Entity Framework
  9. Add a auto increment primary key to existing table in oracle [duplicate]
  10. ways to avoid global temp tables in oracle
  11. Spring Batch ORA-08177: can’t serialize access for this transaction when running single job, SERIALIZED isolation level
  12. How to generate an entity-relationship (ER) diagram using Oracle SQL Developer
  13. String to date in Oracle with milliseconds
  14. Error while trying to retrieve text for error ORA-01804
  15. Create Pivot view in SQL from a SQL table
  16. Does Oracle support full text search?
  17. Oracle.ManagedDataAccess.EntityFramework – ORA-01918: user ‘dbo’ does not exist
  18. Why doesn’t PL/SQL respect privileges granted by Roles?
  19. ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
  20. Can We use threading in PL/SQL?
  21. Spool Command: Do not output SQL statement to file
  22. Speed up to_sql() when writing Pandas DataFrame to Oracle database using SqlAlchemy and cx_Oracle
  23. What is the algorithm used by the ORA_HASH function?
  24. oracle “table or view does not exist” from inside stored procedure
  25. passing in table name as plsql parameter
  26. How to integrate Oracle and Kafka
  27. How to call an Oracle function with a Ref Cursor as Out-parameter from C#?
  28. How to Register Pluggable Database(PDB) with new created LISTENER
  29. Grant Select on all Tables Owned By Specific User
  30. ORA-12704: character set mismatch

Leave a Comment