Clever Geek Handbook

ORA-00932: inconsistent data types: expected INTERVAL DAY SECOND received CHAR

SELECT COALESCE (
      (to_timestamp( '2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS')
        -   ('2014-09-22   09:00:00' ,'yyyy/mm/dd HH24:MI:SS'))  
        - (to_timestamp( '2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS')  
        - to_timestamp('2014-09-22 09:00:00.' ,'yyyy/mm/dd HH24:MI:SS')) 
       , '00:00') 
FROM DUAL;

This works in postgres but doesn't work in oracle.

+3


source to share


3 answers


It looks like you are trying to do math (+, -) with TIMESTAMP. TIMESTAMP doesn't like this. you must CAST TIMESTAMP before DATE:

not bla is blu (where bla and blu are TIMESTAMP)

do
CAST (bla as DATE) - CAST (blu as DATE)
and you will get a NUMBER (multiply it by 3600 * 24 and you will turn it into seconds)


BUT you will lose information in milliseconds

check this link timestamp

You can define TIMESTAMP here

+9


source


Change '00:00'

to INTERVAL '0' DAY

:

SELECT COALESCE (
  (to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')) -
  (to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')),
  INTERVAL '0' DAY)
FROM DUAL;


More information: Interval literals

+2


source


  • You COALESCE

    don't need to test just one expression . NVL

    would be enough. Basically, if there is no time difference, will return 0.
  • DATE

    has a temporary part, the datatype TIMESTAMP

    is an extension to the datatype DATE. In addition to the DATE Datatype elements, the TIMESTAMP data type contains fractions of a second to an accuracy of 0 to 9 decimal places, the default being 6. So it TO_DATE

    makes more sense in your case .
SQL> SELECT NVL(
      2            (to_date( '2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS'))
      3           - (to_date('2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS'))
      4          ,0) DIFF
      5  FROM DUAL
      6  /

          DIFF
    ----------
             0

    SQL>

For other values, to get a significant difference in the time interval:

SQL> SELECT NVL(
  2            (to_date( '2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 23:00:00','YYYY-MM-DD HH24:MI:SS'))
  3           - (to_date('2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS'))
  4          ,0) DIFF
  5  FROM DUAL
  6  /

      DIFF
----------
-.58333333

Refresh

Since date difference returns a number, using NVL

c TO_DATE

will not return interval

, but a number

. Since in the above example it is 0.

To get the spacing in the difference to_timestamp

makes sense. So NVL and TO_TIMESTAMP would be nice:

SQL> SELECT NVL (
  2    (to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')) -
  3    (to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')),
  4    INTERVAL '0' DAY) diff
  5  FROM DUAL
  6  /

DIFF
---------------------------------------------------------------------------
+000000000 00:00:00.000000000
+1


source






More articles:


All Articles