0

My query returns the two fields in the select line. I need to find the difference in HH:MI:SS between the two fields. I keep getting errors when I try DIFF or even TO_DATE. The format for each field is different and the field are in different tables. ALARM_GENERATION_DT is in the PHM_INTERACTION_PERS table and the CALL_START is in the PHM_OUTBOUND table.

The formats for each field are as follows:

ALARM_GENERATION_DT        mm-dd-yyyy hh:mi:ss AM/PM
CALL_START                 yyyy-mm-dd hh24:mi:ss

My current query is:

SELECT  p.alarm_generation_dt, o.call_start
FROM phm_interaction_pers p, phm_outbound o
WHERE p.otb_id = o.record_id
AND o.call_start like '2015-03%';
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Jim Kopycinski
  • 1
  • 3
    What data types are the columns? Different format either implies at least one is a string, or one is a date and the other a timestamp (neither of which have any intrinsic format). – Alex Poole Mar 18 '15 at 15:10
  • or both are strings with a different format. – ruudvan Mar 18 '15 at 16:05
  • call_start from the phm_outbound table is varchar2 (25 byte) and alarm_generation_dt from phm_interaction_pers is DATE, data default = systimestamp – Jim Kopycinski Mar 19 '15 at 19:39
  • **Never**, ever store dates as `varchar` - the problem you have right now stems directly from this bad design decision. –  May 27 '15 at 14:15

1 Answers1

0

this has been answered here

You'll just need to get your varchar2 date into a proper date format in order to do 'date math'.

Community
  • 1
  • 1
Joel Williams
  • 76
  • 5