Counting the number of days excluding sunday between two dates

Question

I am trying to calculate number of days betwen two dates excluding sundays. This is my query,

SELECT   F_PLANHM_END_DT
       - F_PLANHM_ST_DT
       -   2
         * (TO_CHAR (F_PLANHM_END_DT, 'WW') - TO_CHAR (F_PLANHM_ST_DT, 'WW'))
  FROM VW_S_CURV_PROC
 WHERE HEAD_MARK = 'IGG-BLH-BM 221';

SELECT COUNT (*)
  FROM (SELECT SYSDATE + l trans_date
          FROM (    SELECT LEVEL - 1 l
                      FROM VW_S_CURV_PROC
                CONNECT BY LEVEL <= ( (SYSDATE + 7) - SYSDATE)))
 WHERE TO_CHAR (trans_date, 'dy') NOT IN ('sun');

I am retrieving date from a view called VW_S_CURV_PROC with start date : F_PLANHM_ST_DT and end date F_PLANHM_END_DT. Somehow i cant make this to work. Please help me...

This is asked often. Your question is not quite a duplicate - try modifying the code by changing the second select to remove sunday only - in this answer: http://stackoverflow.com/questions/12932965/sql-to-return-the-number-of-working-days-between-2-passed-in-dates — jim mcnamara, Apr 20 '15 at 02:55
hi jim thanks for your referral. so i tried this, http://paste.ofcode.org/8qZDAjzwYFR87emy52GEgS and it says ORA-32033: unsupported column aliasing — Konz Mama, Apr 20 '15 at 03:17
I have already answered a similar question here http://stackoverflow.com/a/28405901/3989608 If you have a set of defined holidays, then you could further use it as answered here http://stackoverflow.com/a/29671333/3989608 — Lalit Kumar B, Apr 20 '15 at 03:19
@LalitKumarB can you post something so that i can make it an answer ? thank you so much lalit — Konz Mama, Apr 20 '15 at 03:27

score 3 · Accepted Answer · answered Apr 20 '15 at 05:44

You could use the ROW GENERATOR technique to first generate the dates for a given range, and then exclude the SUNDAYs.

For example, this query will give me the total count of days between 1st Jan 2014 and 31st Dec 2014, excluding the Sundays -

SQL> WITH DATA AS
  2    (SELECT to_date('01/01/2014', 'DD/MM/YYYY') date1,
  3      to_date('31/12/2014', 'DD/MM/YYYY') date2
  4    FROM dual
  5    )
  6  SELECT SUM(holiday) holiday_count
  7  FROM
  8    (SELECT
  9      CASE
 10        WHEN TO_CHAR(date1+LEVEL-1, 'DY','NLS_DATE_LANGUAGE=AMERICAN') <> 'SUN'
 11        THEN 1
 12        ELSE 0
 13      END holiday
 14    FROM data
 15      CONNECT BY LEVEL <= date2-date1+1
 16    )
 17  /

HOLIDAY_COUNT
-------------
          313

SQL>

Counting the number of days excluding sunday between two dates

1 Answers1