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

Feeds:: Posts; Comments

« SQL SERVER 2012- Shortcut – How to find all system objects related to any file table

SQL SERVER 2012 – Fix – Error : 35343 – CREATE INDEX statement failed. Column has a data type that cannot participate in a columnstore index. Omit column. »

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

April 10, 2013 by Muhammad Imran

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)

Posted in Solutions, SQL SERVER | Tagged raresql, SQL, SQL Server, SQL SERVER - Shorter way to convert Seconds to Day Hour Minute Seconds | 6 Comments

6 Responses

on May 21, 2013 at 10:08 pm | Reply SQL SERVER – Shorter way to convert Minutes to Days, Hours, Minutes | SQL Server Portal

[…] 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), […]
on May 23, 2013 at 8:40 pm | Reply jessica

what changes would need to happen if lets say we wanted to convert hour to minutes? for example all we had was 2.08 hours or 12.34 hours and we wanted to return minutes?
- on May 23, 2013 at 9:24 pm | Reply Muhammad Imran
  
  To achieve this, you need to multiply hour *60. Given below is the script.
  
  Declare @Hour as numeric(18,2)
  Set @Hour=12.34
  Select @Hour*60 as [Minutes]
  –OUTPUT
  Minutes
  —————————————
  740.40
on September 6, 2013 at 9:35 pm | Reply Asad

I need to convert milliseconds to Days, Hours, Minutes, Seconds & milliseconds.
Can we use the same technique ?
on September 6, 2013 at 10:22 pm | Reply SQL SERVER – Shorter way to convert Milliseconds to Days, Hours, Minutes, Seconds & Milliseconds | SQL Server Portal

[…] my earlier article, I wrote the solution Shorter way to convert Seconds to Days, Hours, Minutes, Seconds . Recently, I received a request inquiring how to convert Milliseconds to Days, Hours, Minutes, […]
on March 23, 2022 at 10:00 pm | Reply JJ

OK, but this makes you input a number to convert it… what if i have a a sql query that has results in ‘minutes’ column…and i want to conver That COLUMN results to days, hours and minutes?