One of my friends told me that he had a data set in which numbers are expressed in decimal format.

For example 3.45 means 3 hours and 45 minutes, 4.30 means 4 hours 30 minutes etc.

The challenge is to add these numbers not as decimal values but as time values so adding 3.45 and 4.30 should be 8.15 and not 7.75.

Let us create this data set

create table #t (gtime numeric(8,2))
insert into #t values (3.45),(4.30),(4.45),(4.45)

There are two methods that I come up with

Method 1 : Use CONVERT function

select
convert(varchar(10),
dateadd(minute,sum(floor(gtime)*60+gtime%1*100),0),
108)
as time_value
from
#t

The result is

time_value
----------
17:45:00

Method 2 : CONVERT the sum into TIME

select
cast(dateadd(minute,sum(floor(gtime)*60+gtime%1*100),0) as time)
as time_value
from
#t

The result is

time_value
----------------
17:45:00.0000000