| Author |
 Topic  |
|
|
ZMike
Posting Yak Master
110 Posts |
 Posted - 01/22/2013 : 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 |
Edited by - ZMike on 01/22/2013 18:42:01
|
|
|
ScottPletcher
Yak Posting Veteran
USA
79 Posts |
Posted - 01/22/2013 : 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 - 01/22/2013 : 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
India
47189 Posts |
Posted - 01/23/2013 : 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
Yak Posting Veteran
USA
79 Posts |
Posted - 01/23/2013 : 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
|
Edited by - ScottPletcher on 01/23/2013 10:43:41 |
|
|
| |
Topic |
|
|
|
Milliseconds to Hh:mm:ss
