How can I get the difference in hours between two dates?

Question

I need to do a "select" that returns the difference between the type date field 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 that SYSDATE is already a date, there is no need to use TO_DATE() to convert it to a date.

If you do not 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 date formatting is wrong, you can use two steps, for example:

 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 it like this:

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

You can also check this in a few minutes: Oracle: how to subtract two dates and get minutes of the result

Answer 3

The difference 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 ); 

The difference 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 ); 

Difference between two dates in hours (truncated)

 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;