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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DateTime
Previous Post
SQL SERVER – Convert Old Syntax of RAISEERROR to THROW
Next Post
SQL SERVER – Learn SQL Server 2014 Online in a Day – My Latest Pluralsight Course

Related Posts

12 Comments. Leave new

  • Hi Pinal,
    Alternative use convert 108 style,
    DECLARE @TimeinSecond INT
    SET @TimeinSecond = 86399 — Change the seconds
    SELECT CONVERT(VARCHAR, DATEADD(s, @TimeinSecond, ‘1900/01/01’), 108)

    Reply
  • Hi Pinal,
    Other script,
    DECLARE @TimeinSecond INT
    SET @TimeinSecond = 86399 — Change the seconds
    SELECT CONVERT(VARCHAR, DATEADD(s, @TimeinSecond, 0), 108)

    Reply
  • i want to know how convert second to hours in sql server?

    Reply
  • 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?

    Reply
  • It is taking second more than 6 digit ? Like 83598087. I try it not working for me.

    Reply
  • 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);

    Reply
  • DO NOT USE. DOES NOT WORK.

    Steps to reproduce

    1. Run the code with the variable set as:

    SET @TimeinSecond = 143999

    2. Change the variable to

    SET @TimeinSecond = 863999

    3. In both cases it produces the same result:

    39:59:59

    Reply
  • Remove the first RIGHT

    Reply
  • If is negative?
    Example = – 1580

    Reply
    • For negative and all cases the best option is:
      CAST(@TimeinSecond /3600 AS VARCHAR)+’:’+
      CAST(@TimeinSecond %3600/60 AS VARCHAR)+’:’+
      CAST(@TimeinSecond %60 AS VARCHAR) AS ‘h:m:s’,

      Reply
  • Works great! Thanks!

    Reply
  • I like this solution best:

    DECLARE @TimeinSecond INT
    SET @TimeinSecond = 86401
    select CAST(@TimeinSecond /3600 AS VARCHAR)+’:’+
    format(@TimeinSecond %3600/60, ’00’)+’:’+
    format(@TimeinSecond %60, ’00’) AS ‘h:m:s’

    Reply

Leave a Reply

Menu
Exit mobile version