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)

 

go

 

you will get

0001234

Using this and playing around with some of the string functions 

CREATE FUNCTION [dbo].SecsToTime

      (@nsecs int)

     

RETURNS nvarchar(7)

WITH EXECUTE AS CALLER

AS

-- place the body of the function here

BEGIN

      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

     

      begin

            declare @nHrs nvarchar(5)

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

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

      end

 

-- select dbo.SecsToTime(12345)

      return @rc

END

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)

-------

4:01:05

 

(1 row(s) affected)

Fun!