Milliseconds to Hh:mm:ss - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Milliseconds to Hh:mm:ss

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2013-01-22 : 18:41:16

I was querying the SSRS execution logs and realized the time is in milliseconds I found a lot of forums and answers to convert. However , I wanted to find out what a good efficient way is. I shouldn't have anything over 24 hours but I'd like to prepare for it just in case. This is mainly for display but I was planning of holding it into a table unless I create a function for after the data is stored for display .

note this is just a 2008 server not r2

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-22 : 19:04:00

To me, easiest is:

CONVERT(varchar(8), DATEADD(MILLISECOND, <milliseconds>, 0), 8)

You'd have to add extra code in front to check for > 24 hours.

ZMike
Posting Yak Master

110 Posts

Posted - 2013-01-22 : 21:47:56

Scott,

That's a lot easier than a lot of the ones I saw. Thank you very much it works great. It doesn't look like I have any that are even close to 24 hours. But if someone wouldn't mind adding to Scott's code I'd highly appreciate it as a future reference.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 01:08:16

quote:
Originally posted by ZMike

Scott,

That's a lot easier than a lot of the ones I saw. Thank you very much it works great. It doesn't look like I have any that are even close to 24 hours. But if someone wouldn't mind adding to Scott's code I'd highly appreciate it as a future reference.


DECLARE @MS int
SET @MS=36345--time in milliseconds

--get timediff in hh:mm:ss format
SELECT dbo.GetTimeDiff(0,DATEADD(ms,@MS,0))

GetTimeDiff can be found here

http://visakhm.blogspot.in/2010/03/time-difference-function.html

this will handle cases where hour crosses day mark

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 10:17:29

For consistency, I'd stick to a similar coding style:

ISNULL(CAST(NULLIF(DATEDIFF(DAY, 0, DATEADD(MILLISECOND, milliseconds, 0)), 0) AS varchar(3)) + ' days ', '') +
CONVERT(varchar(8), DATEADD(MILLISECOND, milliseconds, 0), 8)

For example:

SELECT
ISNULL(CAST(NULLIF(DATEDIFF(DAY, 0, DATEADD(MILLISECOND, milliseconds, 0)), 0) AS varchar(3)) + ' days ', '') +
CONVERT(varchar(8), DATEADD(MILLISECOND, milliseconds, 0), 8)
from (
select 86476561 as milliseconds union all
select 1907342
) as test_data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources