You could try the following, substituting in relevant dates in @d1 and @d2:declare @d1 datetime = '3-jan-2000'
, @d2 datetime = '2-may-2000'
select isnull(cast(nullif(case when datepart(month, @d1) > datepart(month, @d2)
then datediff(year, @d1, @d2) - 1
else datediff(year, @d1, @d2)
end, 0) as varchar)
+ case when (select case when datepart(month, @d1) > datepart(month, @d2)
then datediff(year, @d1, @d2) - 1
else datediff(year, @d1, @d2)
end) > 1 then ' Years, ' else ' Year, ' end, '') +
isnull(cast(nullif(case when datediff(day, dateadd(month, datediff(month, @d1, @d2), @d1), @d2) < 0
then (datediff(month, @d1, @d2) - 1) % 12
else datediff(month, @d1, @d2) % 12
end, 0) as varchar)
+ case when (select case when datediff(day, dateadd(month, datediff(month, @d1, @d2), @d1), @d2) < 0
then (datediff(month, @d1, @d2) - 1) % 12
else datediff(month, @d1, @d2) % 12
end) > 1 then ' Months, ' else ' Month, ' end, '') +
isnull(cast(nullif(case when datediff(day, dateadd(month, datediff(month, @d1, @d2), @d1), @d2) < 0
then datediff(day, dateadd(month, datediff(month, @d1, @d2) -1, @d1), @d2)
else datediff(day, dateadd(month, datediff(month, @d1, @d2), @d1), @d2)
end, 0) as varchar)
+ case when (select case when datediff(day, dateadd(month, datediff(month, @d1, @d2), @d1), @d2) < 0
then datediff(day, dateadd(month, datediff(month, @d1, @d2) -1, @d1), @d2)
else datediff(day, dateadd(month, datediff(month, @d1, @d2), @d1), @d2)
end) > 1 then ' Days' else ' Day' end, '')