CODEWITHSUNDEEP
sqlselectdb2decimaldate-formatting
Veljko
Veljko

Reputation: 1808

Convert DECIMAL hours to Hours:Minutes SQL SELECT (without Functions and Declare)

I have decimal field with for example value 0.17 (it is 0 hours and 10 minutes not 17 because 17*60/100=10,2 minutes) I would like to convert to solely to hours and minutes in this format: 0:10

I tried select with SUBSTR but I am loosing 0 value in that case. I would like only pure SELECT without declare and functions!!! Please I cannot use those!

I saw some examples also with TO_DATE functions but this is not what I need because it just converts but without calculation.

Thanks in advance for help.

Upvotes: 0

Views: 527

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Being hours, I prefer 00:10. You can do this using LPAD():

concat(concat(lpad( floor( (value - floor(value))*60), 2, '0'), ':'),
       lpad(mod(floor(value), 2, '0'))
      )

Of course, you can just pad to one character for the hours, if you only want one "0".

Upvotes: 1

Related Questions