sql server - Converting a float number in 0-1 range to time -
in application time info stored in db in float field, where:
0 means 0 am; 0.5 12 0,999305555555556 11:59 pm
the conversion time information done in application ui components designed work way save time info float (this decided before time datatype introduced in sql server 2008r2).
my question is:
how perform conversion?
minute has 60 seconds,
hour has 3600 seconds,
day has 86400 seconds,
multiply (0-1) float 86400 (let's call total_seconds),
1) floor of division of total_seconds 3600 hours
2) subtract hours*3600 total_seconds
3) floor of division of total_seconds 60 minutes
4) subtract minutes*60 total_seconds
6) what's left seconds
declare @raw_input float = 0.999305555555556 declare @total_seconds int declare @hours int, @minutes int, @seconds int set @total_seconds = @raw_input * 86400 set @hours = floor(@total_seconds/3600) set @total_seconds = @total_seconds - @hours * 3600 set @minutes = floor(@total_seconds/60) set @seconds = @total_seconds - @minutes * 60
edit: or simpler, adapting similar question/answer:
declare @raw_input float = 0.999305555555556 declare @secondss decimal = @raw_input*86400 select cast(convert(varchar,dateadd(second, @secondss, 0),108) time) >23:59:00.0000000
Comments
Post a Comment