Convert Seconds to Minutes, Hours and Days in SQL Server

As a developer there are times when yo need to convert seconds to minutes, hours and days. The TSQL script mentioned in this article can be used to easily Convert Seconds to Minutes, Hours and Days.

Learn How to FORMAT SQL Server Dates Using FORMAT Function in SQL Server

For more information on New TSQL Enhancements in SQL Server 2016, Read TSQL Enhancements in SQL Server 2016 for Developers and DBAs.

/* 
  
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. These scripts are very hand and we recommend you bookmark this page to access it whenever you find the need.

Related Articles

• FORMAT SQL Server Date Using FORMAT Function in SQL Server
• TSQL Enhancements in SQL Server 2016 for Developers and DBAs
• 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