Kodyaz Development Resources

Here is two user defined functions that can be used to sum times expressed in hours and minutes like 1.15, 1.30


declare @t1 decimal(10,2), @t2 decimal(10,2)
select @t1 = 1.25, @t2 = 2.45

select dbo.ConvertToHours(dbo.ConvertToMinutes(@t1) + dbo.ConvertToMinutes(@t2))

will return

4.10




ALTER function ConvertToMinutes
(
@Hour decimal(10,2)
)
returns int
-- Input like 1.30 One and half an hour
-- select dbo.ConvertToHours( dbo.ConvertToMinutes(1.30) + dbo.ConvertToMinutes(2.30) )
begin
return FLOOR(@Hour) * 60 + (@Hour % 1) * 100.0
end
GO
ALTER function ConvertToHours
(
@Minutes int
)
returns decimal(10,2)
-- Returns like 1.30 One and half an hour
-- select dbo.ConvertToHours( dbo.ConvertToMinutes(1.30) + dbo.ConvertToMinutes(2.30) )
begin
return CAST( CAST((@Minutes / 60) as varchar(10)) + '.' + CAST((@Minutes % 60) as varchar(10)) as decimal(10,2))
end