PL/SQL Extracting difference between dates in different formats

Question

This is the query I am trying to use:

select a.cr_mdt_abstractor_dt, b.created,
(trunc(a.CR_MDT_ABSTRACTOR_DT - b.created) || ' days and ' ||<br>
        to_char(to_date('01/01/2000', 'MM-DD-YYYY') + (a.CR_MDT_ABSTRACTOR_DT - b.created),
                'HH24:MI:SS'
               )
       ) as Diff
from CR_MDT a,
CR_MDT_VERIFY b
where a.CR_MDT_ID=b.FK_CR_MDT_ID

The error I am getting is:

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

The column b.created is in date format 09-MAR-17 10.52.23.195311 AM(TIMESTAMP) and column for a.cr_mdt_abstractor_dt is in DATE.

Can I get help to correct the query so that column b.created is in converted format to extract from a.cr_mdt_abstractor_dt?

Answer 1

use TRUNC on CR_MDT_ABSTRACTOR_DT (TIMESTAMP) and created separately .

SELECT a.cr_mdt_abstractor_dt,
  b.created,
  TRUNC(a.CR_MDT_ABSTRACTOR_DT) - TRUNC(b.created)
  || ' days and '
  || TO_CHAR(to_date('01/01/2000', 'MM-DD-YYYY') + (a.CR_MDT_ABSTRACTOR_DT - b.created), 'HH24:MI:SS' ) AS Diff
FROM CR_MDT a,
  CR_MDT_VERIFY b
WHERE a.CR_MDT_ID=b.FK_CR_MDT_ID;

Sample output:

CREATED       DIFF
07-09-17    -182 days and 12:15:15