I am getting hours by subtracting time fields in the format hh:mm(00:21) by using this rtrim(char(TIMESTAMPDIFF(8,char(LABTRANS.finishtime - LABTRANS.starttime)))) || ':'|| rtrim(char(mod(int(TIMESTAMPDIFF(4,char(LABTRANS.finishtime - LABTRANS.starttime))),60))) as total_time, ), but i have to show in decimal value ( 0.3500 ). How can i achieve it.

see my sample table : -->create table labtrans(starttime TIME,finishtime TIME) -->insert into labtrans(starttime , finishtime ) values( '08:02 Am','08:42 Am'), ( '07:02 Am','08:42 Am'), ( '01:02 pm','09:02 PM'), ( '06:02 Am','08:00 Am')

2 Answers

0
Mark Barinstein On Best Solutions

When you subtract TIME columns in Db2, there result is DEC(6,0) in the following format: HHMMSS (time duration).
See the Time arithmetic topic for details.
So, if you need to express time duration in the HH+MM/60 format, then you may use the following:

select 
  starttime, finishtime
, finishtime-starttime hhmmss
, int(finishtime-starttime)/10000
+ dec(mod((finishtime-starttime)/100, 100), 27)/60 as "hh+mm/60"
from labtrans

The result is:

STARTTIME   FINISHTIME  HHMMSS   hh+mm/60
---------   ----------  ------   --------
08:02:00    08:42:00      4000     0.6666
07:02:00    08:42:00     14000     1.6666
13:02:00    21:02:00     80000     8.0000
06:02:00    08:00:00     15800     1.9666
0
Paul Vernon On

does this help at all?

values hour(current time) + .01 * minute(current time)

returns

1               
----------------
           10.14

  1 record(s) selected.