Freigeben über

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

  • Artikel

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!

Comments

  • Anonymous
    April 05, 2012
    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.
  • Anonymous
    April 06, 2012
    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.ThanksMike