Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

T-SQL–Converting Seconds to Time

I was working on a small piece of code the other day that was calculating the seconds for an event. I had a function that returned me the seconds as an integer. That’s good, but I wanted to get that value back in minutes and seconds. The scale wasn’t so large as to worry about hours, or days (I hope).

In any case, I could certainly do some math. Takes seconds, divide by 60 to get minutes, and then take the remainder and add that as seconds, concatenate, convert to time. Crazy.

There’s an easier way using CONVERT.

DECLARE @s INT
SELECT
    @s = 325
SELECT
    @s
  , CONVERT(TIME, DATEADD(SECOND, @s, 0));

I can just add the seconds to the 0 time, which is midnight, and I’ll get the time back in the right datatype.

This code gives me 5:25, which is correct. Five minutes and 25 seconds.

If I increase the numbers, say into hours, I can take 4325 like this:

DECLARE @s INT
SELECT
    @s = 4325
SELECT
    @s
  , CONVERT(TIME, DATEADD(SECOND, @s, 0));

And get this;

time


Filed under: Blog Tagged: sql server, syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...