CODEWITHSUNDEEP
oracle-databasedatetimeplsqldate-arithmetic
pwlm
pwlm

Reputation: 184

Oracle PLSQL get difference between 2 datetime fields ignoring days

I would like to find the simplest way of calculating the difference in hours between 2 dates from Oracle datetime fields whilst ignoring the days, months & years portion. For example:

Datetime 1 (DATE variable) = 10/05/2017 16:00:00
Datetime 2 (DATE variable) = 15/05/2017 19:34:23

Required result (NUMBER output) = 3.576 hours

This is formula will be used in a PLSQL procedure, the output needs to be a number as above. I would imagine some combination of TO_DATE & TRUNC might work. Any help would be most appriciated and apologies if this is a duplicate question.

Upvotes: 0

Views: 446

Answers (1)

APC
APC

Reputation: 146199

Use the sssss date mask to get just the time element as the number of seconds since midnight. Then it's just a matter of simple arithmentic:

select (to_number(to_char(datetime2, 'sssss')) 
         - to_number(to_char(datetime1, 'sssss')) / 3600 as diff_hours
from dual;

PL/SQL version is the same....

declare
    Datetime1 DATE := to_date('10/05/2017 16:00:00', 'dd/mm/yyyy hh24:mi:ss');
    Datetime2 DATE := to_date('15/05/2017 19:34:23', 'dd/mm/yyyy hh24:mi:ss');
    hours_diff number;
begin
    hours_diff := (to_number(to_char(datetime2, 'sssss')) 
         - to_number(to_char(datetime1, 'sssss'))) / 3600 ;
    dbms_output.put_line(hours_diff);
end;

Upvotes: 4

Related Questions