1 2 Previous Next 22 Replies Latest reply: Jul 11, 2012 12:39 AM by Purvesh K RSS

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

    Gurujothi
      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
          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
            €$ħ₪
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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