Feeds:
Posts

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

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

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)

### 5 Responses

1. […] 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), […]

2. 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

3. 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 ?

4. […] 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, […]