select 	[Age] = convert(varchar, [Years])  + ' years ' +
		convert(varchar, [Months]) + ' months ' +
		convert(varchar, [Days])   + ' days',
	*
from
(
	select
		[Years] = case	when BirthDayThisYear <= Today
				then datediff(year, BirthYearStart, CurrYearStart)
				else datediff(year, BirthYearStart, CurrYearStart) - 1
				end,
		[Months]= case	when BirthDayThisYear <= Today
				then datediff(month, BirthDayThisYear, Today)
				else datediff(month, BirthDayThisYear, Today) + 12
				end,
		[Days]= case	when BirthDayThisMonth <= Today
				then datediff(day, BirthDayThisMonth, Today)
				else datediff(day, dateadd(month, -1, BirthDayThisMonth), Today)
				end,
		Birth = convert(varchar(10) ,Birth, 121),
		Today = convert(varchar(10), Today, 121)
	from
	(
		select	--BirthDayThisYear  = dateadd(year,  datediff(year,  BirthYearStart, CurrYearStart), Birth), 
			BirthDayThisYear = 
			case	when	day(dateadd(year,  datediff(year,  BirthYearStart, CurrYearStart), Birth))   <> day(Birth)
				then	dateadd(day, 1, dateadd(year,  datediff(year,  BirthYearStart, CurrYearStart), Birth))
				else	dateadd(year,  datediff(year,  BirthYearStart, CurrYearStart), Birth)
				end,
--			BirthDayThisMonth = dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth), 
			BirthDayThisMonth = 
			case 	when 	day(dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) <> day(Birth)
				then	dateadd(day, 1, dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth))
				else	dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)
				end, 
			*
		from
		(
			select	BirthYearStart = dateadd(year, datediff(year, 0, Birth), 0),
				CurrYearStart  = dateadd(year, datediff(year, 0, Today), 0),
				BirthMonthStart = dateadd(month, datediff(month, 0, Birth), 0),
				CurrMonthStart  = dateadd(month, datediff(month, 0, Today), 0),
				*
			from
			(
				-- Load some test date pairs
				select	Birth = convert(datetime, '1960-02-29'),
					--Today = dateadd(day, 0, datediff(day, 0, getdate()))
					Today = convert(datetime, '2006-03-01')		union all
				select	convert(datetime, '2005-03-01'), convert(datetime, '2006-02-27')
			) aaaa
		) aaa
	) aa
)a

quote:

---

Originally posted by thanksfor help

Hi,

I want date diff between 2 dates in the following format.

no.of years, no.of months , no.of days.

For eg. 2-3-2006 and 4-7-2004 of format (mm-dd-yyyy)

The result should be

2 years, 2 months and 4 days.

Thanks

---

select End_Date = dateadd(dd,27,dateadd(mm,9,dateadd(yy,1,'2004-04-07')))

End_Date                                               
------------------------------------------------------ 
2006-02-03 00:00:00.000

(1 row(s) affected)