Right idea, Junglee, but you're going to get 24 months there, when it should be 2 years, 0 months, 0 days.

You'll need to do something like this:

DECLARE @date1 DATETIME,

@date2 DATETIME

SELECT @date1 = '1/1/2008',

@date2 = '4/12/2010'

SELECT

DATEDIFF( mm, @date1, @date2) / 12 AS years

, DATEDIFF( mm, @date1, @date2) % 12 AS months

, DATEDIFF( dd, DATEADD( mm, DATEDIFF( mm, @date1, @date2), @date1), @date2)

Because we're not looking for the actual year crossing of 12/31 - 1/1, you need to derive the # of years from the # of months differentiating the values. Then the # of months is the remainder of the division by 12. The reason for the dateadd/datediff for days is that we have to advance the @date1 forward to just give us a difference in days.

Problem is, this is incomplete. It won't handle inverted days well. To see what I mean, invert the date1/2 to this:

SELECT @date1 = '4/12/2008',

@date2 = '1/1/2010'

If the above solves your problem, well and good. If not, I've got to go find some code that I wrote up at one point to deal with this. It gets really messy.