Format elapsed time

Question

Post reply

Format elapsed time

n.heyen

SSCrazy

Points: 2849
More actions
March 19, 2009 at 8:29 am

#77271

I have a need to return the elapsed time in a specific format. Namely hh:mm:ss.sss without supressing leading zeros. In other words, I need to get the time to look like this:
00:03:14.085
not
0: 3:14. 85
I can get to the latter with this bit of code but before I mess around with a bunch of IF statements to determine the partial string lengths, I'm hoping there is a better way.
[font="Courier New"]SELECT CAST(DATEPART(hour,(@end-@start)) AS CHAR(2))+':'+
CAST(DATEPART(minute,(@end-@start)) AS CHAR(2))+':'+
CAST(DATEPART(second,(@end-@start)) AS CHAR(2))+'.'+
CAST(DATEPART(millisecond,(@end - @start)) AS CHAR(3)) AS ElapsedTime[/font]
the @start and @end vars are DATETIME set with GETDATE()
Thanks!
Norman

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

select

[HH:MM:SS:mmm] = convert(varchar(12),ET-ST,114),

*

from

( -- Test Data

select ST = convert(datetime,'20080318 23:57:33.887') ,

ET= convert(datetime,'20090319 10:45:47.850')

) a

Results:

HH:MM:SS:mmm ST ET

------------ ----------------------- -----------------------

10:48:13.963 2009-03-18 23:57:33.887 2009-03-19 10:45:47.850

You will have to decide what to do in cases where the elapsed time is more than 99 hours. This code will truncate the hours at 2 digits.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

Here is some test code for you to look at:

declare @D1 datetime,

@D2 datetime,

@diff int;

select @D1 = dateadd(ms,-12310235,getdate()), @D2 = getdate();

select @diff = datediff(ms,@D1, @D2);

select @D1, @D2, @diff, @diff % 1000, (@diff / 1000) % 60, ((@diff / 1000)/ 60) % 60, (((@diff / 1000)/ 60) / 60) % 24

n.heyen SSCrazy Points: 2849 More actions · Answer 3

Thank you Micheal, exactly what format I was looking for. I'll have to see if I can figure out just how it works.

And as to what to do with queries running longer than 99 hours? I think hunting the user down and deal with them appropriately...:-)

Thanks!

Norman

Sergiy SSC Guru Points: 110192 More actions · Answer 4

Michael Valentine Jones (3/19/2009)
You will have to decide what to do in cases where the elapsed time is more than 99 hours. This code will truncate the hours at 2 digits.

24 hour actually.

For longer periods you need to count days.

_____________
Code for TallyGenerator