Time Calculation on Numbers
In
Forums sometimes I read questions on how to find out the difference between two times which are stored as float
Here are some possible answers
declare @t1 char(5), @t2 char(5), @sum float
select
@t1='12.56', @t2='7.58'
set
@sum=cast(@t1 as float)-cast(@t2 as float)
--Method 1 : Convert to Datetime
select convert(varchar(5),dateadd(day,0,replace(@t1,'.',':'))- dateadd(day,0,replace(@t2,'.',':')),108)
--Method 2 : Number caculation1
select case when right(@sum,2)>=60 then @sum-0.4 else @sum end
--Method 3 : String caculation
select cast(left(@sum,charindex('.',@sum)-1)+case when right(@sum,charindex('.',reverse(@sum))-1)>60 then right(@sum,charindex('.',reverse(@sum))-1)-40 else right(@sum,charindex('.',reverse(@sum))-1) end/100.0 as decimal(4,2))
--Method 4 : Number calculation2
select cast(cast(@sum as int)+case when cast(@sum*100 as int)%100>=60 then cast(@sum*100 as int)%100-40 else cast(@sum*100 as int)%100 end/100.0 as decimal(4,2))