Calculate difference between 2 date / times in Oracle SQL

You can substract 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

Note:

This answer applies to dates represented by the Oracle data type DATE. Oracle also has a data type TIMESTAMP, which can also represent a date (with time). If you subtract TIMESTAMP values, you get an INTERVAL; to extract numeric values, use the EXTRACT function.

You could use to_timestamp function to convert the dates to timestamps and perform a substract operation.

Something like:

SELECT 
TO_TIMESTAMP ('13.10.1990 00:00:00','DD.MM.YYYY HH24:MI:SS')  - 
TO_TIMESTAMP ('01.01.1990:00:10:00','DD.MM.YYYY:HH24:MI:SS')
FROM DUAL

If you select two dates from 'your_table' and want too see the result as a single column output (eg. 'days - hh:mm:ss') you could use something like this. First you could calculate the interval between these two dates and after that export all the data you need from that interval:

         select     extract (day from numtodsinterval (second_date
                                                   - add_months (created_date,
                                                                 floor (months_between (second_date,created_date))),
                                                   'day'))
             || ' days - '
             || extract (hour from numtodsinterval (second_date
                                                    - add_months (created_date,
                                                                  floor (months_between (second_date,created_date))),
                                                    'day'))
             || ':'
             || extract (minute from numtodsinterval (second_date
                                                      - add_months (created_date,
                                                                    floor (months_between (second_date, created_date))),
                                                      'day'))
             || ':'
             || extract (second from numtodsinterval (second_date
                                                      - add_months (created_date,
                                                                    floor (months_between (second_date, created_date))),
                                                      'day'))
     from    your_table

And that should give you result like this: 0 days - 1:14:55

(TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*60*60*24 sum_seconds,
         (TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*60*24 sum_minutes,
         (TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*24 sum_hours,
         (TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi')) sum_days 

If You want get date defer from using table and column.

SELECT TO_DATE( TO_CHAR(COLUMN_NAME_1, 'YYYY-MM-DD'), 'YYYY-MM-DD') - 
       TO_DATE(TO_CHAR(COLUMN_NAME_2, 'YYYY-MM-DD') , 'YYYY-MM-DD')  AS DATEDIFF       
FROM TABLE_NAME;

select to_char(actual_start_date,'DD-MON-YYYY hh24:mi:ss') start_time,
to_char(actual_completion_date,'DD-MON-YYYY hh24:mi:ss') end_time,
floor((actual_completion_date-actual_start_date)*24*60)||'.'||round(mod((actual_completion_date-actual_start_date)*24*60*60,60)) diff_time
from fnd_concurrent_requests 
order by request_id desc;  

Single query that will return time difference of two timestamp columns:

select INS_TS, MAIL_SENT_TS, extract( hour from (INS_TS - MAIL_SENT_TS) ) timeDiff 
from MAIL_NOTIFICATIONS;

select round( (tbl.Todate - tbl.fromDate) * 24 * 60 * 60 ) from table tbl