# 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 (http://blog.SQLAuthority.com)

## 4 thoughts on “SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format”

1. 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)

Like

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

Like

3. DINESH |

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

Like

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