SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Milliseconds to Hh:mm:ss
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ZMike
Posting Yak Master

110 Posts

Posted - 01/22/2013 :  18:41:16  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
421 Posts

Posted - 01/22/2013 :  19:04:00  Show Profile  Reply with Quote
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.
Go to Top of Page

ZMike
Posting Yak Master

110 Posts

Posted - 01/22/2013 :  21:47:56  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/23/2013 :  01:08:16  Show Profile  Reply with Quote
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/

Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
421 Posts

Posted - 01/23/2013 :  10:17:29  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000