Feeds:
Posts
Comments

Posts Tagged ‘Convert Milliseconds to Days Hours Minutes Seconds and Milliseconds’

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

Posted in Solutions, SQL SERVER, tagged , , , on September 6, 2013| Leave a Comment »

In 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, Seconds & Milliseconds. 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 @Milliseconds as bigint
Declare @SubtractDate as datetime
--Enter Number of Milliseconds here
Set @Milliseconds=91234567
Set @SubtractDate=DateAdd(ms,@Milliseconds,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) ' +
Convert(varchar(10),DatePart(ms,@SubtractDate))
+ ' milliseconds(s) '
AS [Result]
--OUTPUT

Result
——————————————————————————————————–
1 Day(s) 1 Hour(s) 20 Minute(s) 34 Second(s) 567 milliseconds(s)

(1 row(s) affected)

Read Full Post »