Calculate Age using TSQL

This is a modified version from the original post by Michael Valentine Jones Yak DBA Kernel 


CREATE function [dbo].[fn_Age](@START_DATE datetime, @END_DATE datetime)
returns  varchar(9) 
as
-- Original version @http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
-- Calculates age in years, months and days from @START_DATE through @END_DATE and
-- Returns the age in format YYY MM DD.

-- Years is the number of full years between @START_DATE and @END_DATE.
-- Months is the number of full months since the last full year anniversary.
-- Days is the number of days since the last full month anniversary.
BEGIN
--([dbo].[fn_Age](isnull([DOB],getdate()),getdate()))

declare @AGE varchar(9)

declare @AGE_YEARS int
declare @AGE_MONTHS int
declare @AGE_DAYS int

-- Return null if @START_DATE > @END_DATE
IF @START_DATE > @END_DATE BEGIN RETURN @AGE END

SELECT @AGE_YEARS = AGE_YEARS,
@AGE_MONTHS = AGE_MONTHS,
@AGE_DAYS = datediff(dd, dateadd(mm, AGE_MONTHS, dateadd(yy, AGE_YEARS, START_DATE)),END_DATE)
FROM (SELECT AGE_MONTHS = CASE WHEN AnniversaryThisMonth <= END_DATE
THEN datediff(mm,dateadd(yy,AGE_YEARS,START_DATE),END_DATE)
ELSE datediff(mm,dateadd(yy,AGE_YEARS,START_DATE),END_DATE)-1
END, *
FROM (SELECT AGE_YEARS = CASE WHEN AnniversaryThisYear <= END_DATE
THEN datediff(yy,START_DATE,END_DATE)
ELSE datediff(yy,START_DATE,END_DATE)-1
END, *
FROM (SELECT AnniversaryThisYear = dateadd(yy,datediff(yy,START_DATE,END_DATE),START_DATE),
AnniversaryThisMonth = dateadd(mm,datediff(mm,START_DATE,END_DATE),START_DATE), *
FROM (Select START_DATE = dateadd(dd,datediff(dd, 0, @START_DATE),0), END_DATE = dateadd(dd, datediff(dd, 0, @END_DATE),0)) Temp4
) Temp3
) Temp2
) Temp1

SELECT @AGE = right('000'+convert(varchar(4),@AGE_YEARS),3) + ' ' +
right('00'+convert(varchar(4),@AGE_MONTHS),2) + ' ' +
right('00'+convert(varchar(4),@AGE_DAYS),2)

RETURN @AGE
END