Results 1 to 10 of 10
  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
    626
    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,110
    Provided Answers: 5
    Some more copy/paste from Google results, eh?

  5. #5
    Join Date
    Jun 2017
    Posts
    4

    Thumbs up Add TO_NUMBER function

    Hi,

    Use TO_NUMBER in SELECT statement 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_NUMBER(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!!!!!!!!
    Last edited by MJ Bharathy; 06-09-17 at 08:19.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,110
    Provided Answers: 5
    Quote Originally Posted by MJ Bharathy View Post
    Use TO_NUMBER in SELECT statement as below
    Why? Difference of two DATE values is number of days between these two values, so - why would you apply TO_NUMBER to a number?

  7. #7
    Join Date
    Jun 2017
    Posts
    4

    Lightbulb

    Hi

    Difference of two DATE value is NOT NUMBER (Oracle could not convert the datatype, in this scenario).
    Oracle will treat it as DATE datatype only.

    As you can see this in ERROR
    "PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got DATE."

    Oracle expected NUMBER but it got DATE. So i have used TO_NUMBER function to convert it as NUMBER datatype.

    Thanks!!!!

  8. #8
    Join Date
    Feb 2005
    Posts
    59
    Provided Answers: 1
    This is a 7 month old post so suspect it was fixed a while ;o)

    Anyway, the only change needed, which was posted by flyboy above, is to enclose the TO_DATE minus TO_DATE within parenthesis; i.e. select ( to_date(blah) – to_date(blah) ) * 24 * 60

    There is no need to use TO_NUMBER!!!

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,110
    Provided Answers: 5
    Well, I believe that you (MJ Bharathy) are wrong. Difference of two DATE values is a number.

    How many days are there between Jun 28 and Jun 12? Should be 16, right?

    Code:
    SQL> create table test
      2  (col_1 date,
      3   col_2 date);
    
    Table created.
    
    SQL> insert into test (col_1, col_2)
      2  values (date '2017-06-12', date '2017-06-28');
    
    1 row created.
    
    SQL> select col_2 - col_1 difference from test;
    
    DIFFERENCE
    ----------
            16
    
    SQL>
    Can I multiply that value with another number (say, -3)? I'd expect -48 as a result. Of course, that won't work if the difference isn't a number.
    Code:
    SQL> select (col_2 - col_1) * -3 result from test;
    
        RESULT
    ----------
           -48
    
    SQL>
    It appears that it works as well.

    Did you, by any chance, check the Matrix of Datetime Arithmetic? It clearly says that "date - date = number". You can't add, multiply or divide date values, but YES, you can subtract them and the result IS a NUMBER OF DAYS between these two dates.

  10. #10
    Join Date
    Jun 2017
    Posts
    4

    Smile

    Hi...

    Thanks for your explanation.

    Yes, you are right.

    I was misunderstand by the oracle error. Now its clear.

    Thanks!!!!

Posting Permissions

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