SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format
Posted
by Pinal Dave
on SQL Authority
See other posts from SQL Authority
or by Pinal Dave
Published on Sun, 17 Aug 2014 01:30:39 +0000
Indexed on
2014/08/18
16:32 UTC
Read the original article
Hit count: 403
PostADay
|sql
|SQL Authority
|SQL DateTime
|SQL Query
|SQL Server
|SQL Tips and Tricks
|T SQL
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
© SQL Authority or respective owner
