Occasionally there's a requirement for elapsed time as years, months, days, hours, minutes, seconds and milliseconds, such that if you were to add all of these time fractions to the original start datetime using DATEADD, you'd arrive at the end datetime.

Here it is:

CREATE FUNCTION [dbo].[iTVF_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

GO

and here's a simple test harness for it:

-- Testing

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

--reconstituting now from then and output to screen

DECLARE @Now DATETIME = GETDATE();

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

CROSS APPLY dbo.iTVF_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