Oracle SQL Error Invalid Number

Navigation
1

I have the following query, which shows me date and time, recorded as default sysdate of 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 the error of Invalid Number.

I know the reason is the transformation of them into CHAR, but is there any way I could make that difference directly in the query?

    
asked by anonymous 26.08.2016 / 16:11

1 answer

2

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
    
26.08.2016 / 17:30
Error 1093 in MySQL How can I format equations in Word 2013 automatically?