Skip to content

T-SQL–Converting Seconds to Time

July 23, 2014

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.

    @s = 325

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:

    @s = 4325

And get this;


About these ads

From → Blog

  1. Rachmat permalink

    Thanks a lot! We have a lot of times in seconds since midnight, and this is a great way to time cast it.

  2. I’ve used a DATETIME data type to represent DURATION: pretty much what you are doing here. I’m good for durations up to, but just short of, one month.

    If you think about it:
    * Add 2 DATETIME data types together, the first being “start” and the second the duration.
    * Subtract 2 DATETIME fields, the result being the duration.
    * Use CONVERT (like you did here) to display the duration.
    Regarding the CONVERT, I tend to use the 120 format and trim off the left characters as needed. If the duration is greater than a day, I subtract that from the duration to get the formatting correct (if you think about it, you’ll see what I mean).

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 4,768 other followers

%d bloggers like this: