SQL SERVER – Shorter way to convert Seconds to Day Hour Minute Seconds

Posts Tagged ‘SQL SERVER – Shorter way to convert Seconds to Day Hour Minute Seconds’

SQL SERVER – Shorter way to convert Seconds to Days, Hours, Minutes, Seconds

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, SQL SERVER - Shorter way to convert Seconds to Day Hour Minute Seconds on April 10, 2013| 6 Comments »

Today, I came across this question in a forum – How to convert seconds to Day(s), Hour(s), Minute(s), Second(s)? For this we need to do a lot of divisions and remainders to convert Seconds to Day, Hour, Minute, Seconds. Given below is the simple script to convert it without doing any divisions and remainders, using only built-in SQL functions DateAdd, DatePart, DateDiff

--This script will work on SQL Server 2005 and above.
Declare @Seconds as int
Declare @SubtractDate as datetime
--Enter Number of Seconds here
Set @Seconds=9974501
Set @SubtractDate=DateAdd(s,@Seconds,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) ' +
Convert(varchar(10),DatePart(ss,@SubtractDate))
+ ' Second(s) ' AS [Result]
--OUTPUT

Result
——————————————————————————-
115 Day(s) 10 Hour(s) 41 Minute(s) 41 Second(s)

(1 row(s) affected)

Read Full Post »