Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Diff of two datetimestamps

    Hi,

    I have written following code to find diff in minute of two datetimestamp, but while compiling getting below error msg :

    PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got DATE.

    The code is as below :

    declare
    l_v_new_var varchar2(200) := '21-OCT-2016 22:59:00';
    l_v_new_var_1 varchar2(200) := '21-OCT-2016 20:00:00';

    l_n_number number;
    begin
    select to_date(l_v_new_var,'DD MON YYYY HH24:MIS') - to_date(l_v_new_var_1,'DD MON YYYY HH24:MIS') * 24 * 60 into l_n_number from dual;
    dbms_output.put_line(' l_n_number '||l_n_number);
    end;
    /



    Thanks with Regards,
    JD

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    just slightly brush up your math skills and try to compute this expression:
    Code:
    3 - 2 * 24
    If you got anything else than (-45), you are wrong as the multiplication has precedence over subtraction.

    Oracle, like every other language, also uses the operator precedence. It is stated in the chapter with the same name in the SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://docs.oracle.com/en/database/database.html Please, consult it.

    And, for your own comfort and the sanity of those who will have to maintain the code: use parenthesis on the places where ambiguity may occur to make the expression evaluation order clear.

  3. #3
    Join Date
    Sep 2016
    Location
    Pune
    Posts
    16
    Hii ,
    When you subtract two variables of type TIMESTAMP, you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and/or microseconds depending on the platform. If the database is running on Windows, systimestamp will generally have milliseconds. If the database is running on Unix, systimestamp will generally have microseconds.

    1 select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' )
    2* from dual
    SQL> /

    SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD')

    ThankYou !!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Some more copy/paste from Google results, eh?

Posting Permissions

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