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

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

asp.net - Problems sending emails from forum -