SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format
- by Pinal Dave
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 (http://blog.SQLAuthority.com)Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Server, SQL Tips and Tricks, T SQL
