This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Jul 10, 2012 10:39 PM by Purvesh K RSS

How to get the dates between Two dates excluding Saturaday and Sunday

Gurujothi Explorer
Currently Being Moderated
Dear All,
select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt 
    from dual 
    connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1;
The above query returning the following output,
DT
DT
04/25/2012
04/26/2012
04/27/2012
04/28/2012
04/29/2012
04/30/2012
05/01/2012
05/02/2012
05/03/2012
05/04/2012
05/05/2012
here I need to exclude the Dates which comes on 'saturday' and 'sunday' and also the common holiday
Here it is '01-May-2012' and I need the output like the following,
04/25/2012
04/26/2012
04/27/2012
04/30/2012
05/02/2012
05/03/2012
05/04/2012
I need the common query to calculate between any two dates.
Can anyone suggest me?

Thank you,
Regsrds,
gurujothi
  • 1. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    jeneesh Guru
    Currently Being Moderated
    select dt 
    from(
        select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt 
            from dual 
            connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1
            )
    where to_char(dt,'fmday') not in ('sunday','saturday')  
    To exclude, common holidays, you need to have a HOLIDAY table. Then use an OUTER JOIN or NOT IN or NOT EXISTS
  • 2. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    €$ħ₪ Expert
    Currently Being Moderated
    SQL> select to_date('25-04-2012', 'DD-MM-YYYY') + level-1 dt
      2      from dual where to_char(to_date('25-04-2012', 'DD-MM-YYYY')+level-1,'DY') not in ('SUN','SAT')
      3      connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') +1;
    
    DT
    ---------
    25-APR-12
    26-APR-12
    27-APR-12
    30-APR-12
    01-MAY-12
    02-MAY-12
    03-MAY-12
    04-MAY-12
    
    8 rows selected.
    for holidays you need to hard code the values in the where clause
  • 3. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Gurujothi,

    How do you tell if a given date is a holiday? If you have a function, then you can do soemthing like this:
    WITH     all_dates     AS
    (
         select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt 
             from dual 
             connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1
    )
    SELECT     dt
    FROM     all_dates
    WHERE     dt          < TRUNC (dt, 'IW') + 5
    AND     is_holiday (dt)     = 0
    ;
    This does not depend on your NLS settings.

    If you have a table of holidays, then you can do a MINUS operation, or a join.
  • 4. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    V prasad Pro
    Currently Being Moderated
    SELECT DT FROM (select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt 
        from dual 
        connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1
        ) WHERE TO_CHAR(DT,'DD') NOT IN ('05','06');
    
    DT                        
    ------------------------- 
    25-APR-12                 
    26-APR-12                 
    27-APR-12                 
    28-APR-12                 
    29-APR-12                 
    30-APR-12                 
    01-MAY-12                 
    02-MAY-12                 
    03-MAY-12                 
    04-MAY-12                 
    
    10 rows selected
  • 5. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    Gurujothi Explorer
    Currently Being Moderated
    Dear all,
    I Thank you all,
    As you all suggested I used the following code and I got the output,
    and I want to get the count(*) returned rows where can I use the count(*) in this code?
    select dt 
    from(
        select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt 
            from dual 
            connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1
            )
    where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holidays from holiday_dates)
    Thank you.
  • 6. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    Gurujothi Explorer
    Currently Being Moderated
    Dear All,
    I user the following code and I got the count(*) value,
    select count(*) from (select dt
    from(
        select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt 
            from dual 
            connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1
            )
    where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holidays from holiday_dates)) dual;
    Thank you,
    Regards,
    Gurujothi
  • 7. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    theoa Pro
    Currently Being Moderated
    Note that all solutions given above are dependent on your NLS settings, with the exception of Franks solution.
    Maybe that is not a problem, but I would not rely on it.
  • 8. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    873006 Newbie
    Currently Being Moderated
    Hi Gurujothi,

    WITH days AS(
    SELECT i,to_char(i,'fmday') week FROM test11)
    SELECT i,week FROM days
    WHERE upper(week) NOT IN ('SATURDAY','SUNDAY') and to_char(i,'dd-mm-yy')<>to_char('01-05-12');
  • 10. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    Gurujothi Explorer
    Currently Being Moderated
    Hi Everyone,
    My Dates table has 3 columns
    1.Fromdate
    2.Todate
    3.Leave_id

    when am using the following sql statement its working fine if the table has only one record,
    select count(*) from (select dt
    from(
        select to_date(fromdate, 'DD-MM-YYYY') + rownum -1 dt 
            from dates 
            connect by level <= to_date(todate, 'DD-MM-YYYY') - to_date(fromdate, 'DD-MM-YYYY') + 1
            )
    where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holidays from holiday_dates)) dual;
    but when my table(Dates) table has more than 2 rows it showing wrong count,
    COUNT(*)
    59968
    in this code where can I use the "WHERE " clause to calculate based on the 'Leave_id'

    something like,
    select...... where leave_id=(select max(leave_id) from dates)
    Thank you,
    Regards,
    gurujothi

    Edited by: Gurujothi on May 3, 2012 4:35 AM
  • 11. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Gurujothi,

    If "LEVEL <= x" is the only CONNECT BY condition, then you should use a table that has only one row. That worked fine in your original problem, where the two parameters could be considered columns of the one row in dual, but this is a different prolem now.

    You can do something like this:
    WITH     cntr     AS
    (
         SELECT     LEVEL - 1     AS n
         FROM     (
                   SELECT  MAX (todate - fromdate)     AS max_range
                   FROM     dates
              )
         CONNECT BY     LEVEL     <= max_range + 1    -- Not maxrange, as originally posted
    )
    SELECT  COUNT (*)     AS total_work_days
    FROM           dates            d
    JOIN           cntr           c  ON     c.n          <= d.todate - d.fromdate
    LEFT OUTER JOIN      holiday_dates     h  ON      h.holidays  = d.fromdate + c.n
    WHERE     TO_CHAR ( d.fromdate + c.n
              , 'fmDay'
              , 'NLS_DATE_LANGUAGE=ENGLISH'
              )     NOT IN ('Saturday', 'Sunday')
    AND     h.holidays     IS NULL
    ;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using.

    Edited by: Frank Kulash on May 3, 2012 10:24 AM
    Corrected spelling of max_range
  • 12. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    Gurujothi Explorer
    Currently Being Moderated
    Hi Frank,
    Thank you for your instant reply,
    am using oracle 10g,

    I tried your code but got the following error,
         ORA-00904: "MAXRANGE": invalid identifier
  • 13. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Gurujothi wrote:
    Hi Frank,
    Thank you for your instant reply,
    am using oracle 10g,

    I tried your code but got the following error,
         ORA-00904: "MAXRANGE": invalid identifier
    My mistake. In the sub-query, I named the column max_range, but in the super-query, I referenced maxrange (without an underscore). Either spelling will work, but it has to be consistent. I've corrected the earlier message now.

    If you don't post any sample data (CREATE TABLE and INSERT statemnts), then I can't test it. I'lll try to post code that will work, but mistakes like this are inevitable when I can't test.
  • 14. Re: How to get the dates between Two dates excluding Saturaday and Sunday
    Gurujothi Explorer
    Currently Being Moderated
    Hi Frank,
    Sorry for my fault,
    The following is my table description,

    CREATE TABLE  "DATES" 
       (     "FROMDATE" DATE, 
         "TODATE" DATE,
                       "LEAVE_ID" Number(5)
       )
    /
    Insert into dates values('05-02-2012','05-05-2012',1);
    
    Create table holiday_dates(holidays date);
    
    insert into holiday_dates values('01-05-2012');
    Now when I used this query,
    select count(*) from (select dt
    from(
        select to_date(fromdate, 'DD-MM-YYYY') + rownum -1 dt 
            from dates 
            connect by level <= to_date(todate, 'DD-MM-YYYY') - to_date(fromdate, 'DD-MM-YYYY') + 1
            )
    where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holidays from holiday_dates)) dual;
    
    /
    Count(*)
       64
    
    /
    Insert into dates values('01/05/2012','05/05/2012',2);
    /
    Now my table has 2 rows,
    select  *  from dates;
    
    
    FROMDATE     TODATE         LEAVE_ID
    01/05/2012     05/05/2012            1
    05/02/2012     05/05/2012            2
    Now when I used this query,
    select count(*) from (select dt
    from(
        select to_date(fromdate, 'DD-MM-YYYY') + rownum -1 dt 
            from dates 
            connect by level <= to_date(todate, 'DD-MM-YYYY') - to_date(fromdate, 'DD-MM-YYYY') + 1
            )
    where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holidays from holiday_dates)) dual;
    The output is ,
    COUNT(*)
    1987
    How to get the output using the max(leave_id) like the following,
    select..........from... where leave_id=(select max(leave_id) from dates);
    where to add "WHERE" clause in the above query?

    Thank you,

    Regards,
    Gurujothi.

    Edited by: Gurujothi on May 3, 2012 8:43 PM

    Edited by: Gurujothi on May 3, 2012 8:44 PM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points