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;

`TIME` in SQL (and DB2 in particular) isn't a duration, it's "time of day", so you can't cast it. `TIMESTAMPDIFF` will give you the total amount of any one unit, which means you'd have to call it multiple times (once for hours, once for minutes after subtracting hours, ....), but it has a serious flaw: for any two dates more than one month apart, the value will not be correct, by about 24 hours/month. Do you need an accurate value? — Clockwork-Muse, Nov 07 '18 at 00:17
Possible duplicate of [How to get difference from two timestamp in DB2?](https://stackoverflow.com/questions/40894634/how-to-get-difference-from-two-timestamp-in-db2). Although note that, as usual, the database is out to get you and you need to be operating in UTC to get usable answers. There may be other possibilities, as well: for instance, if you're trying to _filter_ rows, you're usually better off adding/subtracting the time from your search range, rather than getting the difference. — Clockwork-Muse, Nov 07 '18 at 00:36

score 1 · Answer 1 · answered Nov 07 '19 at 23:24

VALUES HOURS_BETWEEN(START_DATE, END_DATE)

E.g.

VALUES HOURS_BETWEEN(current timestamp, current_date)"

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

  1 record(s) selected.

1 Answers1