Convert Seconds to Minutes, Hours and Days in SQL Server

Introduction

The TSQL script mentioned in this article can be used to Convert Seconds to Minutes, Hours and Days in SQL Server.

/* 
  
Example:  Where Time Is Given In Seconds 
   
Output:     In “Day(s) : Hour(s) : Minute(s) : Second(s)” Format

*/


DECLARE @Seconds INT = 86200;
SELECT CONVERT(VARCHAR(12), @Seconds / 60 / 60 / 24) 
+ ':' + CONVERT(VARCHAR(12), @Seconds / 60 / 60 % 24) 
+ ':' + CONVERT(VARCHAR(2), @Seconds / 60 % 60) 
+ ':' + CONVERT(VARCHAR(2), @Seconds % 60) AS ' Day(s) : Hour(s) : Minute(s) : Second(s) '

GO


DECLARE @Seconds INT = 86200;

SELECT CONVERT(VARCHAR(12), @Seconds / 60 / 60 / 24) AS ' Day(s) '
,+ CONVERT(VARCHAR(12), @Seconds / 60 / 60 % 24) AS ' Hour(s) '
,+ CONVERT(VARCHAR(2), @Seconds / 60 % 60) AS ' Minute(s) '
,+ CONVERT(VARCHAR(2), @Seconds % 60) AS ' Second(s) '

GO



/* 
  Example: Where Time Given In Seconds is Higher than a Day 
*/


DECLARE @Seconds INT = 90400;
SELECT CONVERT(VARCHAR(12), @Seconds / 60 / 60 / 24) AS ' Day(s) '
,+ CONVERT(VARCHAR(12), @Seconds / 60 / 60 % 24) AS ' Hour(s) '
,+ CONVERT(VARCHAR(2), @Seconds / 60 % 60) AS ' Minute(s) '
,+ CONVERT(VARCHAR(2), @Seconds % 60) AS ' Second(s) '

GO



/* 
   Example: Where Time Given In Seconds is Higher than a 365 Days


*/


DECLARE @Seconds INT = 31555000;
SELECT CONVERT(VARCHAR(12), @Seconds / 60 / 60 / 24) AS ' Day(s) '
,+ CONVERT(VARCHAR(12), @Seconds / 60 / 60 % 24) AS ' Hour(s) '
,+ CONVERT(VARCHAR(2), @Seconds / 60 % 60) AS ' Minute(s) '
,+ CONVERT(VARCHAR(2), @Seconds % 60) AS ' Second(s) '

GO

The above scripts works on SQL Server 2005 and higher versions.

Related Articles

• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT and STRING_ESCAPE T-SQL Enhancement in SQL Server 2016
• SERVERPROPERTY T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS T-SQL Enhancement in SQL Server 2016