Calculate difference between 2 date / times in Oracle SQL -

i have table follows:

filename - varchar creation date - date format dd/mm/yyyy hh24:mi:ss oldest cdr date - date format dd/mm/yyyy hh24:mi:ss 

how can calcuate difference in hours minutes , seconds (and possibly days) between 2 dates in oracle sql?

thanks

you can substract dates in oracle. give difference in days. multiply 24 hours, , on.

sql> select oldest - creation my_table; 

if date stored character data, have convert 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         dual;  diff_hours ----------        2.5 

---

note:

this answer applies dates represented oracle data type date. oracle has data type timestamp, can represent date (with time). if subtract timestamp values, interval; extract numeric values, use extract function.