13 Replies Latest reply: Jan 2, 2011 11:10 PM by 826943 RSS

    Generate linear dates betwen two dates

    826943
      Hi,


      There are two dates as input. need to generate series of dates between those dates. Dates must appear in column wise
      please suggest how to write the query on that.



      Eg: If we enter two dates as start date 1-JAN-2011 and end date as 5-JAN-2010

      It must generate a linear date as 1-JAN-2011 2-JAN-2011 3-JAN-2011 4-JAN-2011 5-JAN-2011

      date must appear as column wise. P|lease let me know


      Thanks
      Sudhir
        • 1. Re: Generate linear dates betwen two dates
          Efficientoracle
          Can u try this

          DECLARE
          lv_v_dates VARCHAR2 (32767);

          CURSOR c1
          IS
          SELECT TO_DATE (:start_date, 'dd-mon-yyyy') + ROWNUM - 1 Dates
          FROM all_tables
          WHERE ROWNUM <=
          TO_DATE (:end_date, 'dd-mon-yyyy')
          - TO_DATE (:start_date, 'dd-mon-yyyy')
          + 1;
          BEGIN
          FOR i IN c1
          LOOP
          BEGIN
          lv_v_dates := Lv_v_Dates|| i.dates;
          END;

          END LOOP;
          dbms_output.put_line(Lv_v_Dates);
          END;
          • 2. Re: Generate linear dates betwen two dates
            Marwim
            Hello Sudhir,

            http://technology.amis.nl/blog/9577/retrieving-the-values-for-domains-in-sql-queries-in-the-oracle-rdbms-months-days-numbers-and-custom-value-sets

            gives examples how to do it with SQL only

            Regards
            Marcus
            • 3. Re: Generate linear dates betwen two dates
              Saubhik
              Are you looking for something like this ?
              SQL> WITH t AS (SELECT to_date('01-DEC-2010','DD-MON-RRRR') + level dates
                2             FROM dual
                3             CONNECT BY LEVEL <= to_date('10-DEC-2010','DD-MON-RRRR') - --This is my end date
                4                      to_date('01-DEC-2010','DD-MON-RRRR') --This is my start date.
                5                      + 1
                6            )          
                7  SELECT ltrim((SYS_CONNECT_BY_PATH(to_char(dates,'DD-MON-RRRR'),' '))) dates
                8  FROM ( SELECT dates, ROW_NUMBER() OVER (ORDER BY dates) rn
                9         FROM t
               10         )
               11  WHERE connect_by_isleaf = 1
               12  START WITH rn=1
               13  CONNECT BY PRIOR rn=rn-1 ;
              
              DATES
              ---------------------------------------------------------------------------------------------------------------------------
              02-DEC-2010 03-DEC-2010 04-DEC-2010 05-DEC-2010 06-DEC-2010 07-DEC-2010 08-DEC-2010 09-DEC-2010 10-DEC-2010 11-DEC-2010
              
              SQL> 
              • 4. Re: Generate linear dates betwen two dates
                Twinkle
                Hi,

                Something like this?
                with t as
                ( 
                  select date '2010-12-01' d1, date '2010-12-10' d2 from dual
                )
                select max(decode (r,1,days,null)) D1,
                max(decode (r,2,days,null)) D2,
                max(decode (r,3,days,null)) D3,
                max(decode (r,4,days,null)) D4,
                max(decode (r,5,days,null)) D5,
                max(decode (r,6,days,null)) D6,
                max(decode (r,7,days,null)) D7,
                max(decode (r,8,days,null)) D8,
                max(decode (r,9,days,null)) D9
                from
                 (select rownum r, d1 + i days
                  from t, xmltable('for $i in 0 to xs:int(D) return $i' passing xmlelement(d, d2-d1)
                                                                        columns i integer path '.'));
                Twinkle
                • 5. Re: Generate linear dates betwen two dates
                  kordirko
                  Try this:
                  SQL> var start_d varchar2( 30 )
                  SQL> var end_d varchar2( 30 )
                  
                  SQL> execute :start_d := '2010-03-05'
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> execute :end_d := '2010-03-10'
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  SELECT to_date(:start_d, 'yyyy-mm-dd') + level - 1 dates
                    2  FROM dual
                    3* CONNECT BY LEVEL <= to_date( :end_d, 'yyyy-mm-dd') - to_date( :start_d, 'yyyy-mm-dd') + 1
                    4  /
                  
                  DATES
                  --------
                  10/03/05
                  10/03/06
                  10/03/07
                  10/03/08
                  10/03/09
                  10/03/10
                  
                  6 rows selected.
                  • 6. Re: Generate linear dates betwen two dates
                    826943
                    How to exclude saturday and sunday in the query. Is there any possible way to check
                    • 7. Re: Generate linear dates betwen two dates
                      Hoek
                      SQL> -- generating sample data:
                      SQL> with t as (
                        2  select to_date('1-JAN-2011', 'dd-mon-yyyy') start_date
                        3  ,      to_date('5-JAN-2011', 'dd-mon-yyyy') end_date
                        4  from   dual
                        5  )
                        6  --
                        7  -- actual query:
                        8  --
                        9  select max(decode(max(lvl), 1, dt)) dt1
                       10  ,      max(decode(max(lvl), 2, dt)) dt2
                       11  ,      max(decode(max(lvl), 3, dt)) dt3
                       12  ,      max(decode(max(lvl), 4, dt)) dt4
                       13  ,      max(decode(max(lvl), 5, dt)) dt5
                       14  from ( select start_date+level-1 dt
                       15         ,      level lvl
                       16         from   t
                       17         connect by level <= end_date-start_date+1
                       18       )
                       19  group by dt;
                      
                      DT1                 DT2                 DT3                 DT4                 DT5
                      ------------------- ------------------- ------------------- ------------------- -------------------
                      01-01-2011 00:00:00 02-01-2011 00:00:00 03-01-2011 00:00:00 04-01-2011 00:00:00 05-01-2011 00:00:00
                      
                      1 row selected.
                      
                      SQL> -- generating sample data:
                      SQL> with t as (
                        2  select to_date('1-JAN-2011', 'dd-mon-yyyy') start_date
                        3  ,      to_date('5-JAN-2011', 'dd-mon-yyyy') end_date
                        4  from   dual
                        5  )
                        6  --
                        7  -- actual query:
                        8  --
                        9  select max(decode(max(lvl), 1, dt)) dt1
                       10  ,      max(decode(max(lvl), 2, dt)) dt2
                       11  ,      max(decode(max(lvl), 3, dt)) dt3
                       12  ,      max(decode(max(lvl), 4, dt)) dt4
                       13  ,      max(decode(max(lvl), 5, dt)) dt5
                       14  from ( select start_date+level-1 dt
                       15         ,      level lvl
                       16         from   t
                       17         connect by level <= end_date-start_date+1
                       18       )
                       19  where to_char(dt, 'dy', 'nls_date_language=american') not in ('sat', 'sun')
                       20  group by dt;
                      
                      DT1                 DT2                 DT3                 DT4                 DT5
                      ------------------- ------------------- ------------------- ------------------- -------------------
                                                              03-01-2011 00:00:00 04-01-2011 00:00:00 05-01-2011 00:00:00
                      
                      1 row selected.
                      • 8. Re: Generate linear dates betwen two dates
                        Saubhik
                        Something like this ?
                        SQL> WITH t AS (SELECT to_date('01-DEC-2010','DD-MON-RRRR') + level dates
                          2             FROM dual
                          3             CONNECT BY LEVEL <= to_date('10-DEC-2010','DD-MON-RRRR') - --This is my end date
                          4                          to_date('01-DEC-2010','DD-MON-RRRR') --This is my start date.
                          5                          + 1
                          6                )          
                          7  SELECT ltrim((SYS_CONNECT_BY_PATH(to_char(dates,'DD-MON-RRRR'),' '))) dates
                          8  FROM ( SELECT dates, ROW_NUMBER() OVER (ORDER BY dates) rn
                          9         FROM t
                         10         WHERE trim(to_char(dates, 'DAY')) != 'SUNDAY'
                         11         AND    trim(to_char(dates, 'DAY')) != 'SATURDAY'
                         12        )
                         13  WHERE connect_by_isleaf = 1
                         14  START WITH rn=1
                         15  CONNECT BY PRIOR rn=rn-1 ;
                        
                        DATES
                        --------------------------------------------------------------------------------
                        02-DEC-2010 03-DEC-2010 06-DEC-2010 07-DEC-2010 08-DEC-2010 09-DEC-2010 10-DEC-2
                        010
                        
                        
                        SQL> 
                        • 9. Re: Generate linear dates betwen two dates
                          Aryan_styles
                          Hi,

                          You can create a function with a return type of table. Use any of the above solution for creating function and then u can use that function anywhere as "Select * from table (cast(<your_function(:date_from, :date_to)> as TABLELIST))"
                          where table list is a TYPE OF TABLE

                          Thanks
                          Aryan
                          • 10. Re: Generate linear dates betwen two dates
                            737905
                            Try this:
                            SQL> with t as (SELECT '01-JAN-2011' col1, '05-JAN-2011' col2 FROM dual)
                            SELECT XMLAGG(XMLELEMENT(d,dates||' ').EXTRACT('//text()')) output FROM
                            (SELECT TO_DATE(col1,'DD-MON-RRRR') + lvl Dates FROM
                            (SELECT col1,col2,level - 1 lvl
                            FROM t
                            CONNECT BY level <= TO_DATE(col2,'DD-MON-RRRR') - TO_DATE(col1,'DD-MON-RRRR') + 1)
                            WHERE TO_CHAR((TO_DATE(col1,'DD-MON-RRRR') + lvl),'DY') <> 'SAT'
                            AND TO_CHAR((TO_DATE(col1,'DD-MON-RRRR') + lvl),'DY') <> 'SUN')
                            
                            output
                            ----------
                            03-JAN-11 04-JAN-11 05-JAN-11
                            
                            SQL>
                            • 11. Re: Generate linear dates betwen two dates
                              Hoek
                              >

                              >
                              It must generate a linear date as 1-JAN-2011 2-JAN-2011 3-JAN-2011 4-JAN-2011 5-JAN-2011
                              date must appear as column wise. P|lease let me know
                              What do you mean with 'column-wise'?
                              Do you want one column or five colums?
                              You will have noticed that the techniques used in the posted examples vary from PIVOT to String Aggregation....
                              • 12. Re: Generate linear dates betwen two dates
                                597312
                                Hi ,

                                Like kordiko mentioned, but you need to alter for eliminate the sat and sundays.
                                SQL> var startdt varchar2(30)
                                SQL> var enddt varchar2(30)
                                SQL> exec :startdt:='2010-12-01'
                                
                                PL/SQL procedure successfully completed.
                                
                                SQL> exec :enddt:='2010-12-12'
                                
                                PL/SQL procedure successfully completed.
                                
                                SQL> ed
                                Wrote file afiedt.buf
                                
                                  1  select dt
                                  2  from (select to_date(:startdt,'yyyy-mm-dd')+level-1 dt
                                  3  from dual
                                  4  connect by level<=
                                  5  (to_date(:enddt,'yyyy-mm-dd') - (to_date(:startdt,'yyyy-mm-dd'))+1)
                                  6* )where to_char(dt,'dy') not in('sat','sun')
                                SQL> /
                                
                                DT
                                ---------
                                01-DEC-10
                                02-DEC-10
                                03-DEC-10
                                06-DEC-10
                                07-DEC-10
                                08-DEC-10
                                09-DEC-10
                                10-DEC-10
                                
                                8 rows selected.
                                Thanks,
                                • 13. Re: Generate linear dates betwen two dates
                                  826943
                                  Hi Twinkel,


                                  This is how i am looking for but you are by default specifying 10 columns as static can this be dynamic. A small change i select more then 10 it must be able to populate.


                                  Please let me know


                                  Thanks
                                  Sudhir