February 25, 2009 at 1:01 am
Hi,
how to convert milliseconds into hours,minutes,seconds and milliseconds.
i have two dates from that i have taken milli seconds using datediff.now i want to display total hours,minutes,sec,millisec in that milli seconds.
Thanks,
Ramu
February 25, 2009 at 2:07 am
ramu4ind (2/25/2009)
Hi,how to convert milliseconds into hours,minutes,seconds and milliseconds.
i have two dates from that i have taken milli seconds using datediff.now i want to display total hours,minutes,sec,millisec in that milli seconds.
For calculating the time difference, directly subtract two dates from each other as
Thanks,
Ramu
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
February 25, 2009 at 2:12 am
krayknot
your answer is not complete.
"Keep Trying"
February 25, 2009 at 2:29 am
February 25, 2009 at 2:33 am
Going back to school
declare @MILI bigint
set @mili= 1*60*60*1000+2010
select @mili
,@Mili/(1000*60*60) as Hour
,(@Mili%(1000*60*60))/(1000*60)as Minutes
,((@Mili%(1000*60*60))%(1000*60))/1000 as Seconds
,((@Mili%(1000*60*60))%(1000*60))%1000 as MiliSecs
John Smith
March 4, 2009 at 7:30 am
ramu4ind (2/25/2009)
Hi,how to convert milliseconds into hours,minutes,seconds and milliseconds.
i have two dates from that i have taken milli seconds using datediff.now i want to display total hours,minutes,sec,millisec in that milli seconds.
Thanks,
Ramu
Heh... lemme ask why you want to do this? Is it for display purposes? If so, then the following will probably do with the understanding that this one only goes up to 24 hours... and there's no need to convert it to milliseconds...
SELECT CONVERT(CHAR(12),@EndDate-@StartDate,114)
If you need more than 24 hours, then the following will do...
SELECT CONVERT(VARCHAR(10),DATEDIFF(hh,0,@EndDate-@StartDate)) --Converts the hours
+RIGHT(CONVERT(CHAR(8),@EndDate-@StartDate,108),6) --Converts mins & secs
AS [HHHHH:MM:SS]
... but, I have to tell you, unless it's for output to a file, you shouldn't do this type of formatting in T-SQL... especially breaking it into separate columns. Certainly, you shouldn't store the data that way. If you need to store the difference in a table, store it as a DateTime datatype... that way, you can add them up later on and display the total either in a GUI or using one of the other conversion methods on this thread for the display.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2011 at 8:16 am
CONVERT(TIME,DATEADD (ms, @YOURMILLISECONDS, 0))
May 13, 2011 at 8:22 am
chris.rogers00 (5/13/2011)
CONVERT(TIME,DATEADD (ms, @YOURMILLISECONDS, 0))
This is sql 2k5, time is not available.
May 13, 2011 at 8:35 am
gutted, sorry.. Should have read the post..
😉
May 13, 2011 at 8:45 am
chris.rogers00 (5/13/2011)
gutted, sorry.. Should have read the post..😉
... should have read the forum the thread was in :hehe:...
January 24, 2012 at 4:22 pm
If you by chance are using sql 2008...this works great for this format hh:mi:ss:mmm
of course sql 2005 is more complicated
select convert(char(12),(convert(time,Dateadd(ms,Duration,0),114))) as 'Duration (hh:mi:ss:mmm)'
May 30, 2012 at 7:54 am
chris.rogers00 (5/13/2011)
CONVERT(TIME,DATEADD (ms, @YOURMILLISECONDS, 0))
You're a star SSC Rookie, completely overlooked the TIME data type.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply