SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format

Here is another question I received via email.

“Hi Pinal,

I have a unique requirement. We measure time spent on any webpage in measure of seconds. I recently have to build a report over it and I did few summations based on group of web pages. Now my manager wants to convert the time, which is in seconds to the format Hour : Minute : Seconds. I researched online and found a solution on stackoverflow for converting seconds to the Minute : Seconds but could not find a solution for Hour : Minute : Seconds.

Would you please help?”

Of course the logic is very simple. Here is the script for your need.

DECLARE @TimeinSecond INT
SET
@TimeinSecond = 86399 -- Change the seconds
SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2)

Here is the screenshot of the resolution:

hms SQL SERVER   Convert Seconds to Hour : Minute : Seconds Format

Reference: Pinal Dave (http://blog.SQLAuthority.com)

6 thoughts on “SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format

  1. Thank you Pinal for the tutorial.

    The script gives wrong result if we compute following

    DECLARE @TimeinSecond INT
    SET @TimeinSecond = 988888888 — Change the seconds
    SELECT RIGHT(‘0’ + CAST(@TimeinSecond / 3600 AS VARCHAR),4) + ‘:’ +
    RIGHT(‘0’ + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2) + ‘:’ +
    RIGHT(‘0’ + CAST(@TimeinSecond % 60 AS VARCHAR),2);

    The alternative could be:

    DECLARE @TimeinSecond INT
    SET @TimeinSecond = 988888888 — Change the seconds
    SELECT
    CONVERT(VARCHAR(12), @TimeinSecond /3600) + ‘ Hour(s), ‘
    + CONVERT(VARCHAR(2), @TimeinSecond /60 % 60) + ‘ Minute(s), ‘
    + CONVERT(VARCHAR(2), @TimeinSecond % 60) + ‘ Second(s).’;

    The only part I did not get is why first one didn’t work for bigger value?

    Like

  2. The reason why this code does not work for bigger value, is because the first RIGHT function extract only 2 digits from the result.

    So, you can simply use the code without this first RIGHT function. The only difference is for results under 10 hours who would appear as 1:00:00 instead of 01:00:00

    DECLARE @TimeinSecond INT
    SET @TimeinSecond = 988888888 — Change the seconds
    SELECT CAST(@TimeinSecond / 3600 AS VARCHAR) + ‘:’ +
    RIGHT(‘0’ + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2) + ‘:’ +
    RIGHT(‘0’ + CAST(@TimeinSecond % 60 AS VARCHAR),2);

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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