How to Convert from Number Seconds to Time String in SQL Server

I’ve been working with a sample timer application on the Windows Phone to track our TechMasters meeting timings. In the user interface I capture the number of seconds that someone spoke, and I’d like to return the value of number seconds formatted as a standard looking time string. In SQL Server there is the DATEPART function that will return the number of minutes and number of seconds, but the problem is that the return value is not zero filled, which isn’t a problem as long as the person spoke at least 10 seconds. I needed to find a way to zero fill a string, and while I’m at it, it would be nice to have a function that does this for me.

In SQL Server we can take advantage of the various string functions like RIGHT, REPLICATE and LTRIM to create a zero filled string. For example if you run


declare @num int

set @num = 1234

select right(replicate('0', 7) + ltrim(@num), 7)




you will get


Using this and playing around with some of the string functions 


      (@nsecs int)


RETURNS nvarchar(7)



-- place the body of the function here


      declare @rc nvarchar(12)

--    declare @nSecs int = 1234


      select @rc = right(replicate('0', 2) + ltrim(datepart(minute, convert(time, dateadd(second, @nsecs, '0:00')))),2) +

       ':' + right(replicate('0', 2) + ltrim(datepart(second, convert(time, dateadd(second, @nsecs, '0:00')))),2)


      if @nsecs > 60 * 60 -- more than 1 hour...then prepend # hrs



            declare @nHrs nvarchar(5)

            set @nHrs = convert(nvarchar,datepart(hour, convert(time, dateadd(second, @nSecs, '0:00'))))

            set @rc = @nHrs + ':' + @rc



--    select dbo.SecsToTime(12345)

      return @rc


After creating the function I can create a view that will return the correctly formatted time for my app as a string by calling the function in the view. So if I run

select dbo.SecsToTime(14465)




(1 row(s) affected)


Comments (2)

  1. Droopydave says:

    I like your function and approach.  Now I need to expand it for times over 24 hours, i.e. DD:HH:MM:SS.  First, it appears as if you only account for 9 hours.  It looks like changing @rc to nvarchar(8) would accomplish this?  I think the calculation is the same, correct?  Accounting for more than 24 hours looks a little more complicated… will start looking into it.

  2. mikebenko says:

    I think changing the return to nvarchar(8) instead of nvarchar(7) should do the trick…also for more than 24 hrs you'd probably need to do something with @nHrs to be larger than nvarchar(5) but the basic logic would apply.



Skip to main content