Jeff, good article. Just to add to it, you can do this:

declare @D1 datetime, @D2 datetime

select @d1 = '02/Nov/06 9:14:21 AM', @d2 = '19/Apr/07 11:52:31 AM'

select datepart(year, @d2-@d1)-1900 as years,

datepart(month, @d2-@d1) as months,

datepart(day, @d2-@d1) as days,

datepart(hour, @d2-@d1) as hours,

datepart(minute, @d2-@d1) as minutes,

datepart(second, @d2-@d1) as seconds

And it will give you a result of how many years, months, days, hours, minutes, seconds, are between the two dates provided.

Get rid of the "declare...select" part and make those input parameters, and you have a function. Change the variables to columns and you have a set-based function.

If you need it, you could also add milliseconds to the final select.

The advantage to this one is that it can be used to create calculated columns that can be selected against in a view, indexed, etc., as needed. Doesn't do the string formatting that Jeff's solution does, which is an advantage if you need this for a Where clause, and a very minor disadvantage if you want formatted data.

The years part of the calculation can also be used to find age.