CODEWITHSUNDEEP
sqloracle-databasedatetimedate-arithmetic
V.TS
V.TS

Reputation: 27

How to find difference between two datetimes in Oracle

How to calculate difference between two datetimes and get answer in hours? Datetime format is MM/DD/YYYY HH:MM:SS

Upvotes: 0

Views: 71

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

When you subtract two DATE values, the result is number of days. Therefore, to get number of hours, you have to multiply the result by 24 (as there are 24 hours in a day).

For example:

SQL> select to_date('12.11.2018 10:00:00', 'dd.mm.yyyy hh24:mi:ss')
  2       - to_date('12.11.2018 08:00:00', 'dd.mm.yyyy hh24:mi:ss') number_of_days
  3  from dual;

NUMBER_OF_DAYS
--------------
    ,083333333

SQL> select (  to_date('12.11.2018 10:00:00', 'dd.mm.yyyy hh24:mi:ss')
  2          - to_date('12.11.2018 08:00:00', 'dd.mm.yyyy hh24:mi:ss')
  3         ) * 24 number_of_hours
  4  from dual;

NUMBER_OF_HOURS
---------------
              2

By the way, format mask you posted is wrong; it is not HH:MM:SS but HH:MI:SS; MM is for months, MI is for minutes.

Upvotes: 1

Related Questions