Oracle SQL Error Invalid Number

Question

I have the query below, which shows me date and time, saved as default sysdate oracle:

SELECT TO_CHAR(DATA, 'DD/MM/YYYY hh24:mi:SS') D1, 
       TO_CHAR(DATA_FIM, 'DD/MM/YYYY hh24:mi:SS') D2 
FROM 
       PCN_ROMANEIO_CHECK 
WHERE ROMANEIO = '1234567'

With the result:

26/08/2016 10:52:37 26/08/2016 10:53:55

It turns out that if I try to calculate the difference between both occurs the error of Invalid Number.

I know the reason is their transformation into CHAR, but there is some way that could make this difference from those dates directly in the query?

Answer 1

You can subtract dates in Oracle. This will give you the difference in days. Multiply by 24 to get hours, and so on.

SQL> select oldest - creation from my_table;

If your date is stored as character data, you have to convert it to a date type first.

SQL> select 24 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi') 
             - to_date('2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')) diff_hours 
       from dual;

DIFF_HOURS
----------
       2.5