CODEWITHSUNDEEP
oracle-databasetimestampdate-arithmetic
Thush
Thush

Reputation: 1125

ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got 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 is working in postgres but it is not working in oracle.

Upvotes: 7

Views: 47675

Answers (3)

ravi chaudhary
ravi chaudhary

Reputation: 625

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

Upvotes: 12

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

  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

Upvotes: 1

Rimas
Rimas

Reputation: 6024

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

Upvotes: 3

Related Questions