ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got CHAR

Question

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 is working in postgres but it is not working in oracle.

Precessional

remove . from this line to_timestamp('2014-09-22 09:00:00.' ,'yyyy/mm/dd HH24:MI:SS')) – Altorilievo

Use to_date, timestamp is not required. Check my answer! – Instinctive

Answer 1

It looks like you are trying to do maths (+, -) with TIMESTAMP. TIMESTAMP doesn't like that. you should CAST the TIMESTAMP to DATE:

rather than bla - 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 the millisecond info

check this link timestamp difference

Here you have the definition of the TIMESTAMP

Altorilievo

there is nothing wrong in using the time part of the DATE. Every date has a time part, thus, subtracting two dates will take care of the time part as well. – Instinctive

yes, there is nothing wrong but (+,-) with to_timestamp is wrong, that is why we have to cast the timestamp to date – Altorilievo

A simple to_date instead of to_timestamp wold suffice. – Instinctive

You can subtract one timestamp from another, as your first link shows; you just get an interval back rather than a number. You can't add them though. – Troytroyer

Answer 2

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 info: Interval Literals

Provision

I don't see the use of COALESCE with a single expression to check. NVL is enough. – Instinctive

As demonstrated by the error using COALESCE() has stopped the OP from potentially being caught out by an incorrect implicit conversion, which NVL() would not have done @Lalit. COALESCE() works with only 2 expressions and as it stops implicit conversion there's no real problem here. – Clone

@Ben, even NVL would throw the error ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got CHAR – Instinctive

@LalitKumarB COALESCE is supported by many SQL servers, NVL is specific to Oracle. So I prefer COALESCE here. – Provision

Answer 3

1. With only one expressions to check, COALESCE is not required. NVL would suffice. Basically, if there is no time difference, it would return 0.
2. DATE has a time part, the TIMESTAMP datatype is an extension on the DATE datatype. In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. So, in your case, TO_DATE makes more sense.

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 of 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

Update

Since the difference of the dates returns a number, using NVL with TO_DATE won't return interval but a number. As, in above example, it is 0.

To get the interval in the difference, to_timestamp makes sense. So, NVL and TO_TIMESTAMP would be good :

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

Instinctive