posted 5/3/2012 by pkumar3 - Views: [1515]
I am in reporting team and once had this requirement. We had one table where Seconds were stored as INT and in report we wanted to convert the seconds to HH:MM:SS format. We already had 1 solution ready with us. It was something like:
--------------------------------------------------------------------------------------------------------------- DECLARE @Seconds INT SET @Seconds = 3800 SELECT CONVERT(VARCHAR(10),@Seconds/3600) +':' + RIGHT('00'+CONVERT(VARCHAR(2),(@Seconds%3600)/60),2) +':' + RIGHT('00'+CONVERT(VARCHAR(2),@Seconds%60),2) AS [HH:MM:SS] GO ----------------------------------------------------------------------------------------------------------------- The output-- 01:03:20
---------------------------------------------------------------------------------------------------------------
DECLARE @Seconds INT SET @Seconds = 3800
SELECT CONVERT(VARCHAR(10),@Seconds/3600) +':' + RIGHT('00'+CONVERT(VARCHAR(2),(@Seconds%3600)/60),2) +':' + RIGHT('00'+CONVERT(VARCHAR(2),@Seconds%60),2) AS [HH:MM:SS] GO ----------------------------------------------------------------------------------------------------------------- The output-- 01:03:20
It was working fine, but my colleagues were looking for something different, something elegant. And I jumped on to help them. I had one solution in mind using CONVERT and Style 108:
---------------------------------------------------------------------------------------------------------------DECLARE @Seconds INT SET @Seconds = 3800 SELECT CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108) AS [HH:MM:SS] GO ---------------------------------------------------------------------------------------------------------------
But problem with the above query is, it fails when number of seconds are more than 86399(there are total 86400 seconds in a day). So if number of seconds are 86400; above query will show 00hours instead of showing 24 hours. See following example:
---------------------------------------------------------------------------------------------------------------DECLARE @Seconds INT SET @Seconds = 86500 SELECT CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108) AS [HH:MM:SS] GO --------------------------------------------------------------------------------------------------------------- -- The output-- 00:01:40
---------------------------------------------------------------------------------------------------------------DECLARE @Seconds INT SET @Seconds = 86500 SELECT CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108) AS [HH:MM:SS] GO ---------------------------------------------------------------------------------------------------------------
-- The output-- 00:01:40
Here the expected output was 24:01:40. So I modified the above query a little and I came up with this:
---------------------------------------------------------------------------------------------------------------DECLARE @Seconds INT,@Hour VARCHAR(10) SET @Seconds = 86500 SET @Hour = DATEDIFF(HH, 0, DATEADD(SS,@Seconds,0)) SELECT STUFF(CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108),1,2,@Hour) AS [HH:MM:SS]GO ---------------------------------------------------------------------------------------------------------------
-- The output-- 24:01:40
Just in case you want to run above query on table:
---------------------------------------------------------------------------------------------------------------SELECT STUFF(CONVERT(VARCHAR(8), DATEADD(SS, Seconds,0),108),1,2, DATEDIFF(HH, 0,DATEADD(SS, Seconds,0))) AS [HH:MM:SS] FROM TableName GO ---------------------------------------------------------------------------------------------------------------
I don’t know which approach is better, rather you tell me which one you liked. Looking at them I think both the queries will give almost identical performance, just that 1st query looks like a Mathematical solution while my approach looks like a SQL solution. If you know any other approach please feel free to share