SQL(DB2) Difference between two dates in hours using Timestampdiff and Cast

Question

If i have two columns with start_date and end_date, how can i find the difference between them using TIMESTAMPDIFF and CAST operator.

Column1 with start_date: 2001-07-11-16.51.40.000000
Column2 with end_date: 2001-07-12-09.21.54.000000

Output: 75301 7h 53min 01sec 7.88

I try something like this

SELECT START_DATE, END_DATE
CAST(TIMESTAMPDIFF(HOUR, START_DATE, END_DATE)) AS TIME
FROM TABLE;

Answer 1

You can find the number of hours between two dates or timestamps in Db2 with HOURS_BETWEEN() https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061478.html so try

VALUES HOURS_BETWEEN(START_DATE, END_DATE)

E.g.

VALUES HOURS_BETWEEN(current timestamp, current_date)"

1          
-----------
         23

  1 record(s) selected.