Here is another question I received via email. The reader had to convert seconds to Hour : Minute : Seconds for a report.
“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:
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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’