MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter


















Trending SQL Server Tips






Convert Seconds to Minutes, Hours and Days in SQL Server

Read Comments   |   Related Topic: TSQL Enhancements in SQL Server 2016 > Next Topic: COMPRESS and DECOMPRESS


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
Convert Seconds to Minutes, Hours and Days in SQL Server

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

Related Tips

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

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server



Learn More...





Last Updated On: August 29, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: