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)
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)
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’,
Works great! Thanks!
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’