Peter E. Kierstead (1/16/2014)
[h4]Try this...[/h4]
Create Function dbo.ElapsedTime
(
@StartTime DateTime,
@EndTime DateTime
)
Returns VarChar(20)
As
Begin
Declare
@st DateTime,
@et DateTime,
@NeedOffset bit;
-- Reality check!
If (@StartTime>@EndTime) Return Null;
Select
-- Get isolated time components for start and end DateTime inputs.
@st='1900-01-01 '+Right(Convert(VarChar,@StartTime,121),12),
@et='1900-01-01 '+Right(Convert(VarChar,@EndTime,121),12),
-- If there is a time inversion set a flag indicating that offsetting is required.
@NeedOffset=Case when @st>@et then 1 else 0 End;
-- The DateTime data-type can represent dates between 1753-01-01 00:00:00.000 and
-- 9999-12-31 23:59:59.998, inclusive. However, DateDiff(ms,...) will throw an Int
-- overflow exception if the differential is close to 25 days, therefore, this method
-- processes the days component separately from the time component.
Return
-- Output the DateTime days differential (0 - 3012153).
Cast
(
DateDiff(dd,@StartTime,@EndTime)-
-- Apply day offset, if necessary.
Case @NeedOffset when 1 then 1 else 0 End
as VarChar
)+
':'+
-- Output the DateTime time differential (00:00:00.000 - 23:59:59.999).
-- This is done by replacing both input value's date component with
-- '1900-01-01' before running the appropriate conversion functions.
Right
(
Convert
(
VarChar,
DateAdd
(
ms,
DateDiff
(
ms,
@st,
)+
-- Handle milliseconds offset, if necessary.
Case @NeedOffset when 1 then 86400000 else 0 End,
0
),
121
),
12
);
End
As I said, I'm always up for alternate methods and very much appreciate your time in posting that code. I did try it on the million row test table and it took over 26 seconds to complete where the same code (both executed on my slower 12 year old box) only took a little over 3 . Part of the problem is that it's a scalar function, which will usually mean that, all else being equal, it will run about 7 times slower than direct code or an iSF (proverbial "Inline Scalar Function", which is really "just" an Inline Table Valued Function that returns a scalar value).
While that doesn't seem to be a big difference to many, things like this really start to add up in the face of scalability.
--Jeff Moden
Change is inevitable... Change for the better is not.