How can I get the difference in hours between two

Question

I have to do a "select" that returns the difference between a field of type date from the database and the current date in hours. How can I do this in oracle?

I try this:

   select 24 * (to_date(sysdate, 'YYYY-MM-DD hh24:mi') 
         - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) diff_hours 
   from dual;

But that did not work.

Answer 1

The error is because SYSDATE is already a date, there's no need to use TO_DATE() to convert it to a date.

If you don't convert it to a date:

select
    24 * (sysdate - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;

And if the formatting of the dates are wrong, you can possible use two steps like:

select
    24 * (to_date(to_char(sysdate, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;

Answer 2

I did this way:

select to_number(to_char(sysdate,'HH24')) - to_number(to_char(*YOUR_DATA_VALUE*,'HH24')),max(exp_time)  from ...

Also, you can check this for minutes : Oracle : how to subtract two dates and get minutes of the result

Answer 3

Diference between two dates in hours and minutes

SELECT     TRUNC(minutes_ / 60) || ' Hours, ' || TRUNC(MOD(minutes_,  60)) || ' Minutes'  diference
FROM     ( SELECT  (sysdate - to_date('16/10/2019 18:45:00', 'DD/MM/YYYY HH24:MI:SS')) * 24 * 60     AS minutes_
  FROM     dual
);

Diference between two dates in hours

SELECT     ROUND(minutes_ / 60, 2) || ' Hours ' 
FROM     ( SELECT  (sysdate - to_date('16/10/2019 18:45:00', 'DD/MM/YYYY HH24:MI:SS')) * 24 * 60     AS minutes_
  FROM     dual
);

Diference between two dates in hours (truncate)

SELECT     ROUND(minutes_ / 60, 2) || ' Hours ' 
FROM     ( SELECT  (sysdate - to_date('16/10/2019 18:45:00', 'DD/MM/YYYY HH24:MI:SS')) * 24 * 60     AS minutes_
  FROM     dual
);

Answer 4

select trunc((sysdate - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) / 24)
  from dual;