Three options:
i> Get timespan in seconds from SP level. Convert it in aspx level
Console.WriteLine(TimeSpan.FromSeconds(70165))
ii> Convert it directly in SP level.
declare @i int
set @i = 3683
–set @i = 56769
–set @i = 663719 — more than 100 hours
select replace(str(@i/3600,len(ltrim(@i/3600))+abs(sign(@i/35999)-1)) + ‘:’ + str((@i/60)%60,2) + ‘:’ + str(@i%60,2) ,‘ ‘,‘0′) as ‘raw’
–O/P: 01:01:23
iii> Function fordifferent formats
ALTER FUNCTION [dbo].[fn_FormatTotalTime]( @TimeInSecond INT,
@Format_type VARCHAR(25)
)
RETURNS VARCHAR(40) AS
BEGIN
– SET @SecondsToConvert = 31536000
– Declare variables
DECLARE @Hours int
DECLARE @Minutes int
DECLARE @Seconds int
DECLARE @f_hrs VARCHAR(20)
DECLARE @f_mnt VARCHAR(20)
DECLARE @f_sec VARCHAR(20)
DECLARE @Time VARCHAR(40)
– Set the calculations for hour, minute and second
SET @Hours = @TimeInSecond/3600
SET @Minutes = (@TimeInSecond % 3600) / 60
SET @Seconds = @TimeInSecond % 60
SELECT @f_hrs = CASE WHEN LEN(CONVERT(VARCHAR(10),@Hours))= 1
THEN ‘00′+ CONVERT(VARCHAR(10),@Hours)
WHEN LEN(CONVERT(VARCHAR(10),@Hours))= 2
THEN ‘0′+ CONVERT(VARCHAR(10),@Hours)
ELSE CONVERT(VARCHAR(10),@Hours)
END,
@f_mnt = CASE WHEN LEN(CONVERT(VARCHAR(2),@Minutes))= 1
THEN ‘0′+ CONVERT(VARCHAR(2),@Minutes)
ELSE CONVERT(VARCHAR(2),@Minutes)
END,
@f_sec = CASE WHEN LEN(CONVERT(VARCHAR(2),@Seconds))= 1
THEN ‘0′+ CONVERT(VARCHAR(10),@Seconds)
ELSE CONVERT(VARCHAR(10),@Seconds)
END
– To show 0 for two digits only
IF(LTRIM(RTRIM(@Format_type)) = ‘HH:MM:SS’ )
BEGIN
SELECT @f_hrs = CASE WHEN LEN(CONVERT(VARCHAR(10),@Hours))= 1
THEN ‘0′+ CONVERT(VARCHAR(10),@Hours)
ELSE CONVERT(VARCHAR(10),@Hours)
END
END
– Display the @Time variable as per format
SELECT @Time = CASE WHEN LTRIM(RTRIM(@Format_type)) = ‘HHH:MM:SS’
THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘:’ +CONVERT(CHAR(2), @f_mnt) + ‘:’ +CONVERT(CHAR(2), @f_sec)
WHEN LTRIM(RTRIM(@Format_type)) = ‘HH:MM:SS’
THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘:’ +CONVERT(CHAR(2), @f_mnt) + ‘:’ +CONVERT(CHAR(2), @f_sec)
WHEN LTRIM(RTRIM(@Format_type)) = ‘HHH:MM’
THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘:’ +CONVERT(CHAR(2), @f_mnt)
WHEN LTRIM(RTRIM(@Format_type)) = ‘hours,min,sec’
THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘ hours ‘ +CONVERT(CHAR(2), @f_mnt) + ‘ min ‘ +CONVERT(CHAR(2), @f_sec) + ‘ sec’
WHEN LTRIM(RTRIM(@Format_type)) = ‘hours,min’
THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘ hours ‘ +CONVERT(CHAR(2), @f_mnt) + ‘ min’
END
RETURN(@Time)
END
