0

Could You please help me with convert float to time?
I have values (number of hours) which look like: 

[- 104.59 / -104:35:00],  [0.25 / 00:15:00],  [5,84  /05:50:00]

I can't find solution, because on the similar questions there weren't case with "+" and "-" values.

Black_Dragon_85
  • 1
  • 2
  • 3
    Possible duplicate of [Convert float to datetime](https://stackoverflow.com/questions/15672134/convert-float-to-datetime) – ttwis Nov 20 '17 at 09:52
  • Well... what time should `[- 104.59 / -104:35:00]` represent? – Nick.Mc Nov 20 '17 at 11:16
  • "-104.59" its mean: 104 hours and 0.59 of 1 hour (0.59 * 60 min = 35 min ). Number of hours is ok what makes difficulties to me is to convert 0.59 to minutes in shortest way, whe I could have "-" values. – Black_Dragon_85 Nov 20 '17 at 11:36
  • What I watn to see is: 104:35:00 (104 hours, 35 minutes, second doesn't matter). – Black_Dragon_85 Nov 20 '17 at 11:42

1 Answers1

0
with cte as (select * from (values ('-104.59'), ('0.25'), ('5,84')) t(val))
select val, concat(iif(charindex(':',result)>3,'','0'), result) result
from 
  cte
  cross apply (select replace(val, ',', '.') rp) t
  cross apply (select charindex('.', rp) ind) t1
  cross apply (select concat(substring(rp, 1, ind-1), ':', cast(substring(rp, ind+1, len(rp)) as int)*60/100, ':00') result) t2
tobiasegli_te
  • 1,413
  • 1
  • 12
  • 18
uzi
  • 4,118
  • 1
  • 15
  • 22