Search

Monday, December 15, 2014

Convert Seconds to HH:MM:SS

Use below function to convert Seconds to HH:MM:SS

Create Function dbo.GetHHMMSS(@InputSecs BIGINT) RETURNS nVarChar(Max)
Begin

Declare @HHMMSS nVarChar(Max)

If @InputSecs < 60 and @InputSecs<>0
Begin
SET @HHMMSS = '0:01:00'
End
Else
Begin

SET @HHMMSS = ISNULL(CAST(@InputSecs/3600 AS nVarChar(MAX)) + ':' + RIGHT('0' + CAST(ROUND(CAST((@InputSecs % 3600.0) / 60 AS float),0) AS nVarChar(MAX)),2) + ':' + RIGHT('0' + CAST(ROUND(CAST((@InputSecs % 60.0) AS float),0) AS nVarChar(MAX)),2) ,'0:00:00')

End

Return @HHMMSS
End

Example:
SELECT dbo.GetHHMMSS(500) 

You can also use below query to get above result:

DECLARE @SECONDS INT = 5000

SELECT CONVERT(CHAR(8),DATEADD(second,@SECONDS,0),108) 'TOS HHMMSS'

No comments:

Post a Comment