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)
SQL Server Portal 