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.
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 intSET @MS=36345--time in milliseconds--get timediff in hh:mm:ss formatSELECT dbo.GetTimeDiff(0,DATEADD(ms,@MS,0)) GetTimeDiff can be found herehttp://visakhm.blogspot.in/2010/03/time-difference-function.htmlthis will handle cases where hour crosses day mark------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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:SELECTISNULL(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 |
|
|
|
|
|
|
|