5 Replies Latest reply: May 5, 2008 5:49 AM by 561093 RSS

    List of days between two dates

    586714
      Hi,

      I have 2 dates (in bind variables), say 01-May-2008 and 05-May-2008.

      How can I list all the days between these dates like:

      days
      01-May-2008
      02-May-2008
      03-May-2008
      04-May-2008
      05-May-2008

      I want to fill a cursor with these dates to process.

      Thank you for any hints,

      Roger
        • 1. Re: List of days between two dates
          Anurag Tibrewal
          One possible solution
          select rownum - 1 + to_date('01-May-2008', 'dd-mon-yyyy') a
            from all_objects
          where rownum < to_date('05-May-2008', 'dd-mon-yyyy') -
                 to_date('01-May-2008', 'dd-mon-yyyy') + 2
          • 2. Re: List of days between two dates
            592249
            Refer the link below

            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:185012348071

            regards,
            Abuthalip Khan
            • 3. Re: List of days between two dates
              588246
              Please check the below code

              select * from (select rno||'-'||to_char(:p_start_date,'MON-YYYY') date_val from(
                                            Select rownum rno
                                       From
                                       (
                                       select 1 from dual
                                       Group by cube(0,0,0,0,0,0)
                                       )
                                       where rownum < to_char(last_day(:p_start_Date),'DD'))
                                       )
              Where date_val between :p_start_date and :p_end_date

              Thanks & Regards
              • 4. Re: List of days between two dates
                586714
                Hello,

                Thanks for the quick and valuable responses!

                Hava a nice day,

                Roger
                • 5. Re: List of days between two dates
                  561093
                  Hi,

                  This query runs fine when both the dates fall in the same month but fails when month/year are different.
                  SQL> select * from (select rno||'-'||to_char(to_date('25-04-2008', 'DD-MM-YYYY'),'MON-YYYY') date_va
                  l from(
                    2  Select rownum rno
                    3  From
                    4  (
                    5  select 1 from dual
                    6  Group by cube(0,0,0,0,0,0)
                    7  )
                    8  where rownum < to_char(last_day(to_date('25-04-2008', 'DD-MM-YYYY')),'DD'))
                    9  )
                  10  Where date_val between to_date('25-04-2008', 'DD-MM-YYYY') and to_date('05-05-2008', 'DD-MM-YYY
                  Y');

                  DATE_VAL
                  ----------------------------------------------------------------------------------------------------
                  25-APR-2008
                  26-APR-2008
                  27-APR-2008
                  28-APR-2008
                  29-APR-2008

                  SQL> select to_date('25-04-2008', 'DD-MM-YYYY') + rownum -1 dt
                    2  from dual
                    3  connect by level <= to_date('05-05-2008', 'DD-MM-YYYY') - to_date('25-04-2008', 'DD-MM-YYYY') + 1;

                  DT
                  -----------
                  25-APR-2008
                  26-APR-2008
                  27-APR-2008
                  28-APR-2008
                  29-APR-2008
                  30-APR-2008
                  01-MAY-2008
                  02-MAY-2008
                  03-MAY-2008
                  04-MAY-2008
                  05-MAY-2008

                  11 rows selected.

                  SQL>