IFOBJECT_ID('dbo.udf_SCC_TimeToString') IS NOT NULL
DROP FUNCTION dbo.udf_SCC_TimeToString ;
GO
CREATEFUNCTION dbo.udf_SCC_TimeToString
(
@timesec INT) --input in seconds as integer
RETURNSVARCHAR(25)
/*==========================================
Dom Horton
17/10/2012
==========================================*/
AS
BEGIN
DECLARE
@return
AS VARCHAR(25),
@a AS INT,
@b AS INT,
@hours AS INT,
@mins AS INT,
@secs AS INT;
SET@a = @timesec%3600 --get no. of seconds over the hour
SET@hours = (@timesec - @a)/3600 --get no. of seconds of completed hours, divide by hour in seconds to get completed hours
SET@b = @a --hold no. of secs over the hour
SET@a = @b%60--get no. of secs over the min
SET@mins = (@b - @a)/60 --get no. of seconds of completed mins, divide by min in secs to get completed minutes
SET@secs = @a
SET@return =
CASE
WHEN @timesec >=3600
THEN
convert(varchar(10),@hours) + ' hr ' +
convert(varchar(10),@mins) + ' min ' +
convert(varchar(10),@secs) + ' sec '
WHEN @timesec >=60
THEN
convert(varchar(10),@mins) + ' min ' +
convert(varchar(10),@secs) + ' sec '
ELSE
convert(varchar(10),@secs) + ' sec '
END;
RETURN
@return
END
Example below showing the Stored Procedure in use:
and in use in a SSRS report:
