Technical Article

Convert float to duration-time string

,

This UDF is using in reporting where lots of durations are calculated, stored and then summed up and averaged for reports.  By storing the durations as floats (which are similar to the fixed point numbers datetimes are stored as) math functions are simpler.

The problem is that float-times are not human readable, and cast(@floatime as datetime) will produce a date rather than a duration.

This UDF returns "[d days ]hh:mm:ss"

create function dbo.float2timestring (@floattime float)
returns varchar(20)
as
begin
return case 
when @floattime < 1 then convert(varchar,cast(@floattime as datetime),108)
else cast(floor(@floattime) as varchar) + ' days ' + 
convert(varchar,cast(@floattime - floor(@floattime) as datetime),108) 
end
end
go

Rate

Share

Share

Rate