Here's a function I worked on a year or two back, complete with test harness. Have fun.

ALTER FUNCTION [dbo].[il_ElapsedTime]

/*

Calculate the elapsed time between two datetimes

as year, month, day, hour, minute, second, millisecond

such that adding these values using DATEADD to the earlier

value will yield the later value.

*/

(@Then DATETIME, @Now DATETIME)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

SELECT

y.Years,

mo.Months,

r.[Days],

r.[Hours],

r.[Minutes],

r.Seconds,

r.Milliseconds

FROM (

SELECT

DATESTRING = CONVERT(VARCHAR(8),@Then,112)+' '+CONVERT(VARCHAR(12),@Then,14),

PARMSTRING = CONVERT(VARCHAR(8),@Now,112)+' '+CONVERT(VARCHAR(12),@Now,14)

) ds

CROSS APPLY (SELECT [Years] = DATEDIFF(YEAR,@Then,@Now) - CASE WHEN SUBSTRING(DATESTRING,5,17) > SUBSTRING(PARMSTRING,5,17) THEN 1 ELSE 0 END) y

CROSS APPLY (SELECT [YearAdjDate] = DATEADD(YEAR,y.[Years],@Then)) y4

CROSS APPLY (SELECT [Months] = DATEDIFF(MONTH,y4.YearAdjDate,@Now) - CASE WHEN SUBSTRING(DATESTRING,7,15) > SUBSTRING(PARMSTRING,7,15) THEN 1 ELSE 0 END) mo

CROSS APPLY (

SELECT

[Days] = DATEDIFF(DAY,DATEADD(MONTH,mo.[Months],y4.YearAdjDate),@Now) - CASE WHEN SUBSTRING(DATESTRING,9,13) > SUBSTRING(PARMSTRING,9,13) THEN 1 ELSE 0 END,

[Hours] = DATEPART(HOUR,@Now-@Then),

[Minutes] = DATEPART(MINUTE,@Now-@Then),

[Seconds] = DATEPART(SECOND,@Now-@Then),

[Milliseconds] = DATEDIFF(MILLISECOND,DATEADD(SECOND,(CASE WHEN DATEPART(MILLISECOND,@Then) > DATEPART(MILLISECOND,@Now) THEN -1 ELSE 0 END),DATEADD(MILLISECOND,DATEPART(MILLISECOND,@Then),DATEADD(MILLISECOND,0-DATEPART(MILLISECOND,@Now),@Now))),@Now)

) r

WHERE @Now > @Then

-- Testing

-- 42,963 rows / 00:00:01 including generating sample data,

-- reconstituting now from then and output to screen

DECLARE @Now DATETIME = GETDATE()-18;

WITH SampleData AS (

SELECT TOP(211+DATEDIFF(DAY,'19000101',GETDATE()))

[Then] = DATEADD(MILLISECOND,ABS(CHECKSUM(NEWID()))%86400000,DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,'19000101'))

FROM

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) h (n)

)

SELECT

s.[Then],

[Now] = @Now,

q.*

,c.Calc

FROM SampleData s

OUTER APPLY dbo.il_ElapsedTime (s.[Then], @Now) q

-- include this for testing only

CROSS APPLY (

SELECT Calc =

DATEADD(MILLISECOND,q.[Milliseconds],

DATEADD(SECOND,q.[Seconds],

DATEADD(MINUTE,q.[Minutes],

DATEADD(HOUR,q.[Hours],

DATEADD(DAY,q.[days],

DATEADD(MONTH,q.months,

DATEADD(YEAR,q.Years,s.[Then]))))))) ) c

WHERE @Now <> Calc