# 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.

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 SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video

• äº‚é¦¬å®¢
August 20, 2014 7:06 am

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)

• äº‚é¦¬å®¢
August 20, 2014 7:27 am

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

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

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

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

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

• 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

• Remove the first RIGHT

• If is negative?
Example = – 1580

• 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’,