Converting Decimal time to HH:MM:SS
SolHillJun 22 2010 — edited Jun 22 2010I have a seeded oracle elements tables containing hours in a decimal format. Such as:
35.33333333333333333333
This equates to 35 hours 20 mins, and is displayed as such in the Oracle financials front end. (35:20). I am writing a report to pull out these hours, however I’m getting rounding issues.
Select numtodsinterval(35.33333333333333333333, 'HOUR' ) from dual
Returns: +000000001 11:19:59.999999999
from this I can extract the minutes and seconds then concatenate with trunc(35.33333333333333333333) which gives me 35:19 (not what I needed)
I have tried extracting the seconds and rounding the number which gives me 60 but this can be used date arithmetic as a seconds value to add it to the number as seconds are 1-59
There must be a way of doing this, over all I need to be confident that what I return is going to match what the users see on the front end so writing something custom without knowing the real mechanics behind the figure displayed by oracle won’t give me what I need.
Hope somebody can help
Sol