How to subtract two dateTime field containing dateTime in ISO format and get the result in hours?

Question

I have tried subtracting two date fields but it has just subtracted date and not taken time into consideration

to_number(
 TRUNC(to_timestamp(T1.attribute_2,'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'))-
 TRUNC(to_timestamp(T2.attribute_2,'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'))
)

Date 1 2019-04-26 10:00pm Date 2 2019-04-26 8:00pm Expected Outcome: Date1- Date 2 = 2(in hrs) Actual Outcome: Date1- Date 2 should give 0

What is the _datatype_ of `T1.attribute_2`? You're missing the point if you don't specify the datatypes. In your example your subtracting the same value from itself so the result will always be zero — Nick.Mc, Apr 26 '19 at 00:07
That is not a data type. Please take some time to understand what a datatype is. This is particularly important in a database — Nick.Mc, Apr 26 '19 at 00:12
Two date fields are getting subtracted from two different tables having date in ISO format — Gaurav Kanodia, Apr 26 '19 at 00:12
Oh sorry I missed the different tables bit. You need to specify the datatype. Is it `varchar2`? — Nick.Mc, Apr 26 '19 at 00:13
There are a few suggestions here, but if you are working in databases, particularly with dates, you really need to understand the concept of datatypes. https://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql — Nick.Mc, Apr 26 '19 at 00:32

score 1 · Accepted Answer · answered Apr 26 '19 at 00:34

If you want to take the hours into consideration, then don't truncate the values! TRUNC() removes the time component.

For hours, multiply the difference by 24:

(to_timestamp(T1.attribute_2,'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"')-
 to_timestamp(T2.attribute_2,'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"')
) * 24

How to subtract two dateTime field containing dateTime in ISO format and get the result in hours?

1 Answers1