This discussion is archived
1 2 Previous Next 22 Replies Latest reply: May 17, 2013 4:56 AM by EdStevens RSS

Date range calculation

user11237452 Newbie
Currently Being Moderated
Hi everyone,

I have a SQL as below to calculate the old data in history table based on date range, it getting error
PL/SQL: ORA-00904: "APR": invalid identifier
INSERT INTO MSG_LOG (SELECT * FROM MSG WHERE (SYSDATE - 16-APR-13-20) >10);
DELETE FROM MSG WHERE (SYSDATE - 16-APR-13-20) >10; 
The date value 16-APR-13 actually stores in a variable ln_date with datatype DATE, why it couldn't perform calculation with SYSDATE?
  • 1. Re: Date range calculation
    jeneesh Guru
    Currently Being Moderated
    When you pass a date value, you need to pass it as a string and should use TO_DATE to conevrt it to date

    to_date('16-APR-2013','dd-MON-yyyy')
    When you pass as
    SYSDATE - 16-APR-13-20
    It will be read as =>

    sysdate minus 16 minus APR minus 13 miinus 20.

    And ORACLE dont know, What APR is..

    Edited by: jeneesh on May 16, 2013 9:46 AM

    Your code will finally be,
    INSERT INTO MSG_LOG 
    (
     SELECT * 
     FROM MSG 
     WHERE (SYSDATE - to_date('16-APR-2013','dd-MON-yyyy') - 20) >10 
    );
    
    DELETE FROM MSG 
    WHERE (SYSDATE - to_date('16-APR-2013','dd-MON-yyyy')-20) >10; 
  • 2. Re: Date range calculation
    JamunaJawahar Newbie
    Currently Being Moderated
    +16-APR-13+ should be to_date('16-APR-2013','DD-MON-YYYY')

    Edited by: Jamuna Jawahar on May 16, 2013 9:47 AM
  • 3. Re: Date range calculation
    Manik Expert
    Currently Being Moderated
    I am sure you are getting that date dynamically from somewhere.. But the approach should be like this,
    INSERT INTO MSG_LOG
       (SELECT *
          FROM MSG
         WHERE (SYSDATE - (TO_DATE ('16-APR-13', 'DD-MON-YY')) - 20) > 10);
    
    
    DELETE FROM MSG
          WHERE (SYSDATE - (TO_DATE ('16-APR-13', 'DD-MON-YY')) - 20) > 10;
    
    COMMIT;
    Cheers,
    Manik.
  • 4. Re: Date range calculation
    Etbin Guru
    Currently Being Moderated
    Maybe:
    insert into msg_log (select * from msg where (sysdate - date '2013-04-16' - 20) > 10);
    delete from msg where (sysdate - date '2013-04-16' - 20) > 10; 
    Reards

    Etbin

    Oops! should have checked before posting
  • 5. Re: Date range calculation
    user11237452 Newbie
    Currently Being Moderated
    It works if i manually run the script by manually passing the date with to_date. However, when I corporates with my code variable, the error still the same.

    DECLARE

    ln_date DATE;
    sql_stmt VARCHAR2 (300);
    BEGIN

    sql_stmt :=
    'BEGIN INSERT INTO '
    ||hist_tbl
    || ' (SELECT * FROM '
    || pi_table_name
    || ' WHERE (SYSDATE - '
    || TO_DATE(ln_date,'dd/mm/yyyy')
    || '-'
    ||20
    || ') >'
    ||10
    || '); DELETE FROM '
    || pi_table_name
    || ' WHERE (SYSDATE - '
    || TO_DATE(ln_date,'dd/mm/yyyy')
    || '-'
    ||20
    || ') >'
    ||10
    || '; end;';

    EXECUTE IMMEDIATE sql_stmt;
    END;
  • 6. Re: Date range calculation
    jeneesh Guru
    Currently Being Moderated
    You should use BIND Variables..Like
    --Not tested
    
    sql_stmt :=
    'BEGIN INSERT INTO '
    ||hist_tbl
    || ' (SELECT * FROM '
    || pi_table_name
    || ' WHERE (SYSDATE - :dt1 -20 ) > 10 );'
    || ' DELETE FROM '
    || pi_table_name
    || ' WHERE (SYSDATE - :dt2 - 20 ) > 10 ;'
    || ' end;';
    
    EXECUTE IMMEDIATE sql_stmt using ln_date,ln_date;
    First of all, do you actually need dynamic SQL?

    Edited by: jeneesh on May 16, 2013 10:12 AM
  • 7. Re: Date range calculation
    user11237452 Newbie
    Currently Being Moderated
    I need to run the dynamoc SQL in a loop based on some parameter in the loop cycle? What is :dt1 and :dt2?
  • 8. Re: Date range calculation
    Shahzad.H.Magsi Newbie
    Currently Being Moderated
    jeneesh wrote:
    You should use BIND Variables..Like
    --Not tested
    
    sql_stmt :=
    'BEGIN INSERT INTO '
    ||hist_tbl
    || ' (SELECT * FROM '
    || pi_table_name
    || ' WHERE (SYSDATE - :dt1 -20 ) > 10 );'
    || ' DELETE FROM '
    || pi_table_name
    || ' WHERE (SYSDATE - :dt2 - 20 ) > 10 ;'
    || ' end;';
    
    EXECUTE IMMEDIATE sql_stmt using ln_date,ln_date;
    First of all, do you actually need dynamic SQL?

    Edited by: jeneesh on May 16, 2013 10:12 AM
    but Jeneesh kindly note that unless we are using bind variables in report as a formula column then only we don't need formatting of the bind variable as we will be returning it in a format specified in that CF

    else as a plsql block
    || ' WHERE (SYSDATE - :dt1 -20 ) > 10 );'
    || ' WHERE (SYSDATE - :dt2 - 20 ) > 10 ;'
    should be written as
    ||'WHERE((SYSDATE - to_date(&dt1,'DD/MM/RRRR')) - 20) > 10;
    ||'WHERE((SYSDATE - to_date(&dt1,'DD/MM/RRRR')) - 20) > 10;
    Regards
  • 9. Re: Date range calculation
    jeneesh Guru
    Currently Being Moderated
    Shahzad.H.Magsi wrote:
    but Jeneesh kindly note that unless we are using bind variables in report as a formula column then only we don't need formatting of the bind variable as we will be returning it in a format specified in that CF
    I feel, you assumed the OP is using ORACLE reports builder..
    >
    else as a plsql block
    || ' WHERE (SYSDATE - :dt1 -20 ) > 10 );'
    || ' WHERE (SYSDATE - :dt2 - 20 ) > 10 ;'
    should be written as
    ||'WHERE((SYSDATE - to_date(&dt1,'DD/MM/RRRR')) - 20) > 10;
    ||'WHERE((SYSDATE - to_date(&dt1,'DD/MM/RRRR')) - 20) > 10;
    Regards
    Did not work with reports for a long time..

    Any how you could take the value int a variable first and then bind it
    ln_date := to_date(&dt1,......);
    Then bind the above variable in the dynamic SQL (If dynamic is required)
  • 10. Re: Date range calculation
    user11237452 Newbie
    Currently Being Moderated
    not really understand. How should I create the variable to bind the ln_date and assign it properly in the dynamic SQL?
  • 11. Re: Date range calculation
    user11237452 Newbie
    Currently Being Moderated
    Below is my dynamic SQL. I assigned the date the variable ln_date which datatype is DATE. I try with and without TO_DATE the result still the same. Oracle treat as SYSDATE - 10-APR-13-20, so 'APR' is invalid.
    sql_stmt :=
                'BEGIN INSERT INTO '
             || 'MSG_LOG'
             || ' (SELECT * FROM '
             || 'MSG'
             || ' WHERE (SYSDATE - '
             || (TO_DATE(ln_date,'dd/mm/yyyy'))
             || '-' 
             || 20
             || ') >'
             ||10    
             || '); DELETE FROM '
             || 'MSG'
             || ' WHERE (SYSDATE - '
             || (TO_DATE(ln_date,'dd/mm/yyyy'))
             || '-' 
             || 20
             || ') >' 
             ||10
             || '; end;';
  • 12. Re: Date range calculation
    EdStevens Guru
    Currently Being Moderated
    user11237452 wrote:
    Below is my dynamic SQL. I assigned the date the variable ln_date which datatype is DATE. I try with and without TO_DATE the result still the same. Oracle treat as SYSDATE - 10-APR-13-20, so 'APR' is invalid.
    sql_stmt :=
    'BEGIN INSERT INTO '
    || 'MSG_LOG'
    || ' (SELECT * FROM '
    || 'MSG'
    || ' WHERE (SYSDATE - '
    || (TO_DATE(ln_date,'dd/mm/yyyy'))
    || '-' 
    || 20
    || ') >'
    ||10    
    || '); DELETE FROM '
    || 'MSG'
    || ' WHERE (SYSDATE - '
    || (TO_DATE(ln_date,'dd/mm/yyyy'))
    || '-' 
    || 20
    || ') >' 
    ||10
    || '; end;';
    so if LN_DATE is already a DATE, why are you passing it to TO_DATE?
    see http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions203.htm#i1003589

    Purpose

    TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.
  • 13. Re: Date range calculation
    user11237452 Newbie
    Currently Being Moderated
    No, If I do not pass it to TO_DATE, it still getting 'APR' is invalid. What should be the correct parsing?
    sql_stmt :=
                'BEGIN INSERT INTO '
             || 'MSG_LOG'
             || ' (SELECT * FROM '
             || 'MSG'
             || ' WHERE (SYSDATE - '
             || ln_date
             || '-' 
             || 20
             || ') >'
             ||10    
             || '); DELETE FROM '
             || 'MSG'
             || ' WHERE (SYSDATE - '
             || ln_date
             || '-' 
             || 20
             || ') >' 
             ||10
             || '; end;';
  • 14. Re: Date range calculation
    Etbin Guru
    Currently Being Moderated
    Maybe
    sql_stmt := 'BEGIN INSERT INTO MSG_LOG (SELECT * FROM MSG WHERE (SYSDATE - :the_date - 20) > 10); ' ||
                '      DELETE FROM MSG WHERE (SYSDATE - :the_date - 20) > 10; ' ||
                'END; ';
                
    ln_date := date '2013-04-16';
    
    execute immediate sql_stmt using ln_date,ln_date;
    Regards

    Etbin
1 2 Previous Next

Legend

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