Time Calculation on Numbers
In Forums sometimes I read questions on how to find out the difference between two times which are stored as characters
Here are some possible answers
Subtraction
declare
@t1 varchar(5), @t2 varchar(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))
Addition
--Method 1: Date calculation
select convert(varchar(5),dateadd(day,0,replace(@t1,'.',':'))+ dateadd(day,0,replace(@t2,'.',':')),108)
--Method 2: Number calculation
select @sum=cast(@t1 as decimal(4,2))+@t2
select
case when right(@t1,2)*1+right(@t2,2)>=60 then @sum+0.4 else @sum end