Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: Iterating over dates in For Loop

    Hi All,

    I am trying to iterate over dates in FOR loop but am getting an error "PLS-00382: expression is of wrong type".
    I am trying to do something like this:

    lv_d_start_date DATE := trunc(sysdate-1);
    lv_d_end_date DATE := trunc(sysdate);
    lv_d_date DATE ;

    FOR lv_d_date IN lv_d_start_val_date..lv_d_end_val_date
    LOOP
    <code>
    END LOOP;

    Am i doing an error in syntax somewhere? Is my code valid?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You can not iterate through DATES - boundaries must be numbers (integers, actually). So, either convert date into Julian date (which is a number), or choose some other strategy.
    Code:
    SQL> select sysdate, to_number(to_char(sysdate, 'j')) julian_date from dual;
    
    SYSDATE    JULIAN_DATE
    ---------- -----------
    05.01.2010     2455202
    
    SQL>
    Here's one of possible solutions:
    Code:
    SQL> begin
      2    for cur_r in (select trunc(sysdate, 'yyyy') + level datum
      3                  from dual
      4                  connect by level <= (sysdate - trunc(sysdate, 'yyyy'))
      5                 )
      6    loop
      7      dbms_output.put_line(cur_r.datum);
      8    end loop;
      9  end;
     10  /
    02.01.2010
    03.01.2010
    04.01.2010
    05.01.2010
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Last edited by Littlefoot; 01-05-10 at 05:02.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    The error is quite self-explanative, and, if you would consult the PL/SQL User's Guide and Reference, available e.g. online on http://tahiti.oracle.com/ , you could find that only integer are allowed for LOOP bounds.
    For 10gR2, it is covered here

    I would however LOOP through number of days from sysdate, something like
    Code:
    SQL> begin
      2    for i in -1..0 loop
      3      -- do here anything with trunc(sysdate+i), e.g. print using
      4      dbms_output.put_line( trunc(sysdate+i) );
      5    end loop;
      6  end;
      7  /
    04-01-2010
    05-01-2010
    
    PL/SQL procedure successfully completed.
    
    SQL>

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Or convert to another date format which is numeric, and can cross year boundaries: yyyymmdd

    Code:
    lv_n_start_date NUMBER(8) := TO_CHAR(trunc(sysdate-1), 'YYYYMMDD');
    lv_n_end_date   NUMBER(8) := TO_CHAR(trunc(sysdate),   'YYYYMMDD');
    
    
    FOR i IN lv_d_start_val_date..lv_d_end_val_date
    LOOP
    <code>
    END LOOP;
    --=cf

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by chuck_forbes View Post
    Or convert to another date format which is numeric, and can cross year boundaries: yyyymmdd

    Code:
    lv_n_start_date NUMBER(8) := TO_CHAR(trunc(sysdate-1), 'YYYYMMDD');
    lv_n_end_date   NUMBER(8) := TO_CHAR(trunc(sysdate),   'YYYYMMDD');
    
    
    FOR i IN lv_d_start_val_date..lv_d_end_val_date
    LOOP
    <code>
    END LOOP;
    --=cf
    Bad idea chuck the loop (for example) would go

    for i in 20091128 .. 20091201 loop

    which is not 5 days. Try the following

    Code:
    lv_d_start_date  DATE := trunc(sysdate-1);
       lv_d_end_date    DATE := trunc(sysdate);
       lv_d_date          DATE ;
    
    
    FOR i IN  0 .. lv_d_end_val_date - lv_d_start_val_date 
    LOOP
    <code using lv_d_start_val_date + i >
    END LOOP;
    Last edited by beilstwh; 01-06-10 at 10:55.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Uhhh ... yeah ... that was a pretty horrible idea. Good catch beilstwh.
    --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •