SQL Server Portal

In one of my earlier articles I wrote about  How to convert seconds to Day(s), Hour(s), Minute(s), Second(s) . Today I am going to talk about creating a simple script to convert the Minutes to Day(s), Hour(s) & Minute(s). Generally, to achieve this result, you need to perform lots of divisions and remainders and then convert Minutes to Day, Hour, Minute. Given below is the simple script to convert Minutes without doing any divisions and remainders, but using only built-in SQL functions DateAdd, DatePart, DateDiff

--This script will work on SQL Server 2005 and above.
DECLARE @Minutes AS INT
DECLARE @SubtractDate AS DATETIME
--Enter Minutes here
SET @Minutes=2990
SET @SubtractDate=DATEADD(mi,@Minutes,GETDATE()) - GETDATE()

SELECT CONVERT(VARCHAR(10),DATEDIFF(DAY,'1900-01-01',@SubtractDate))
+ ' Day(s) ' +
CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate))
+ ' Hour(s) ' +
CONVERT(VARCHAR(10),DATEPART(mi,@SubtractDate))
+ ' Minute(s) ' AS [Result]
--OUTPUT

Result
———————————
2 Day(s) 1 Hour(s) 50 Minute(s)

(1 row(s) affected)