Connect With MyTechMantra.com









"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."




Convert Seconds to Minutes, Hours and Days in SQL Server


August 29, 2014


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.


Share this Article

LEARN MORE...
 
Winners
White Papers
Product Reviews
Trending News
All Articles
Free Tools
 
Follow Us...

Related Articles…