How to calculate exact hours between two datetime fields?-CodePudding

I need to calculate hours between datetime fields and I can achieve it by simply doing

select date1,date2,(date1-date2) from table; --This gives answer in DD:HH:MM:SS format
select date1,date2,(trunc(date1)-trunc(date2))*24 --This doesn't take into account the time, it only gives hours between two dates.

Is there a way I can find the difference between date times that gives the output in Hours as a number?

CodePudding user response：

Use the DATEDIFF function in sql.

Example: SELECT DATEDIFF(HOUR, '2021-09-05 12:00:00', GETDATE());

CodePudding user response：

You can find it using the differnece of dates and multiplying with 24

 select date1
       ,date2
       ,(date1-date2)*24  as diff_in_hrs
  from table

CodePudding user response：

The 'format' comment on your first query suggests your columns are timestamps, despite the dummy column names, as the result of subtracting two timestamps is an interval. Your second query is implicitly converting both timestamps to dates before subtracting them to get an answer as a number of days - which would be fractional if you weren't truncating them and thus losing the time portion.

You can extract the number of hours from the interval difference, and also 24 * the number of days if you expect it to exceed a day:

extract(day from (date1 - date2)) * 24   extract(hour from (date1 - date2))

If you want to include fractional hours then you can extract and manipulate the minutes and seconds too.

You can also explicitly convert to dates, and truncate or floor after manipulation:

floor((cast(date1 as date) - cast(date2 as date)) * 24)

db<>fiddle demo