CODEWITHSUNDEEP
sqldatabaseoracle-database
Gaurav Kanodia
Gaurav Kanodia

Reputation: 87

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

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

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

Upvotes: 1

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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

Upvotes: 1

Related Questions