0

I would like to know how to get the difference between two date times. The issue I am facing is that I have to convert the date from the table, to a usable date. i.e. 7841433540 converts to 09/10/14 09:19:00. My sql to return those values is:

SELECT ddt.tochar(ENC_START_DDT,'MM/DD/YY HH24:MI:SS') "Admit", TO_CHAR(SYSTIMESTAMP,'MM/DD/YY HH24:MI:SS') "Today" from CCDBA.PATIENT where CCDBA.PATIENT.PAT_SEQ = '101067048';

Now I thought I could...

SELECT  ddt.tochar(ENC_START_DDT,'MM/DD/YY HH24:MI:SS')-TO_CHAR(SYSTIMESTAMP,'MM/DD/YY HH24:MI:SS')

from CCDBA.PATIENT where CCDBA.PATIENT.PAT_SEQ = '101067048';

But that returns:

ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause:
*Action:

Any help is greatly appreciated

user2037188
  • 17
  • 6
  • Does the information at this page help? http://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql – RaifMT Nov 28 '14 at 14:25
  • I had seen that, but the answer below worked, I had forgotten to convert to a date. I'll get there, a little at a time! – user2037188 Nov 28 '14 at 14:43

1 Answers1

0

You cannot subtract strings and expect the database to understand them as dates. So, convert them to dates:

select (to_date(ddt.tochar(ENC_START_DDT,'MM/DD/YY HH24:MI:SS'), 'MM/DD/YY HH24:MI:SS') -
        sysdate) as diff
from CCDBA.PATIENT
where CCDBA.PATIENT.PAT_SEQ = '101067048';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786