Oracle - Calculate the difference between dates and extract days and hours only in Oracle-CodePudding

I have a query that calculates the difference between two dates and returns a decimal date. I would just like to extract days and hours from the final calculated date.

This is my query.

select sysdate - (to_date('24/AUG/2021 14:00:00', 'DD/MON/YYYY HH24:MI:SS')) as FinalDate from dual;

FinalDate
162.013252314814814814814814814814814815

How do I get my desired output:?

Desired output
| Days     | Hours |
| -------- | ------|
|162       |0.24   |

CodePudding user response：

A little bit of arithmetic.

SQL> with temp (finaldate) as
  2    (select sysdate - (to_date('24/AUG/2021 14:00:00', 'DD/MON/YYYY HH24:MI:SS')) from dual)
  3  select trunc(finaldate) as days,
  4         round((finaldate - trunc(finaldate)) * 24, 2) as hours
  5  from temp;

      DAYS      HOURS
---------- ----------
       162       6,49

SQL>

Why your and my hours don't match? Because of time difference; it's

SQL> select sysdate from dual;

SYSDATE
-------------------
02.02.2022 20:30:04

SQL>

over here.