extract a decimal(5,2) time field URGENT!!

[shefea]

Hi i have a time field in DB2 Database that is storing time as a decimal(5,2) where the max value is 6 for the .XX minute part. How can I go about putting this into a number minutes format or number of hours and minutes format?


Someone suggested the folowing but its for Oracle not db2...

Hallo shefea,

select to_char(to_date('01011900 '||to_char(dutysc,'99.00'),'DDMMYYYY HH24.MI'),
'DD.MM.YYYY HH24:MI:SS') duty from yourtable


Tuesday, March 18, 2003, 10:37:42 AM, you wrote:
ss> I am hoping someone can help me to convert records like these which
ss> represent number of hours into proper time hh:mm
ss> the maximum appears to be 6 for the minutes column.
ss> In the sample below
ss> 11.3 is 11.3 hours and 12.11 is 12.11 hours?
ss> both columns are defined as decimal(5,2)

ss> DUTYSC DUTY
ss> 11.3 12.11
ss> 11 10.56
ss> 50.5 51.37
ss> 20.45 21.03

 

[MarcLodge]

Hi shefea,
Try running this on your db2 platform and see if it gives you the sort of thing you are after

SELECT SUBSTR(DIGITS(11.51),1,2)||
':'||
SUBSTR(DIGITS(11.51),3,2)
FROM SYSIBM.SYSDUMMY1

hth
Marc

 

[Dagon]

select left('10.75', locate('.', '10.75')-1) || ':' || char(cast((0.6 * cast(right('10.75', length('10.75') - locate('.', '10.75')) as decimal(12,5))) as integer))
from sysibm.sysdummy1

Gives:

10.45