ORACLE sql hours between two timestamps format

Question

I have table:

CREATE TABLE B(
...
date1 TIMESTAMP(6),
date2 TIMESTAMP(6),
...);

and I want to get hours between two timestamps. So I run query:

SELECT (date1 - date2)*24 from B;

Result looks like this: +000000024 00:00:00.000000 but I want something like 24:00. How I can format the query? Thanks

score 2 · Answer 1 · answered May 07 '15 at 18:48

2

SELECT to_char((date1 - date2)*24,'HH24:MI') from B;

answered May 07 '15 at 18:48

valentin

score 0 · Accepted Answer · answered May 07 '15 at 18:46

0

Just use a subquery to extract the hours from the expression

SELECT  extract( hour from x) hours FROM
(SELECT (date1 - date2)x FROM B)

answered May 07 '15 at 18:46

Mihai

2 Answers2