Try this (function posted below) the script:
DECLARE @people AS TABLE
(person VARCHAR(20)
,dob DATE)
DECLARE @SDate AS DATE = '5/3/2013' ;
INSERT INTO @people (person,dob)
VALUES
('Frank','3/20/1990'),('Joey','12/31/2000'),('Sue','2/6/2012'),('Mary','4/1/2013'),
('Bil','5/3/2012'),('Bob','1/1/2013'),('Will','10/1/2012')
SELECT
*
FROM
@People p
CROSS APPLY
dbo.itvfCalcAge(p.dob,@SDate)
CREATE FUNCTION dbo.itvfCalcAge
(
@StartDate DATETIME
,@EndDate DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
-- CREATE DATE DEFAULTS
WITH cteDefaults
(
CurrDate,
CurrYrStartDate,
FirstDOMCurrMonth,
LastDOMCurrMonth,
FirstDOMStartDate,
LastDOMEndDate,
FirstDOYCurrYr,
LastDOYCurrYr
)
AS
(
SELECT
GETDATE() AS CurrDate
,(CASE
WHEN DATEDIFF(DAY,DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate),GETDATE()) > 0
THEN DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate)
ELSE
DATEADD(YEAR,-1,DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate))
END)
AS CurrYrStartDate
,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AS FirstDOMCurrMonth
,DATEADD(MILLISECOND,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)) AS LastDOMCurrMonth
,DATEADD(MONTH,DATEDIFF(MONTH,0,@StartDate),0) AS FirstDOMStartDate
,DATEADD(MILLISECOND,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,@EndDate)+1,0)) AS LastDOMEndDate
,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0) AS FirstDOYCurrYr
,DATEADD(MILLISECOND,-3,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())+1,0)) AS LastDOYCurrYr
),
-- DETERMINE THE MOST RECENT ANNIVERSARY DATE AND NEXT ANNIVERSARY DATE.
-- THIS WILL BE USED FOR DETERMINING THE DECIMAL PART OF AGE.
cteAnniversaries (LastAnniversary,NextAnniversary)
AS
(
SELECT
CurrYrStartDate AS LastAnniversary
,DATEADD(YEAR,1,CurrYrStartDate) AS NextAnniversary
FROM
cteDefaults
),
-- DETERMINE THE NUMBER OF DAYS BETWEEN THE ANNIVERSARY DATES.
-- THIS WILL TAKE INTO ACCOUNT LEAP YEARS
cteDaysPerYear(NumDaysCurrYear,TotalYears,TotalMonths,TotalDays,RecentAnniversary)
AS
(
SELECT
DATEDIFF(DAY, a.LastAnniversary, a.NextAnniversary)
AS NumDaysCurrYear
,DATEDIFF(YEAR,@StartDate,@EndDate)
AS TotalYears
,DATEDIFF(MONTH,a.LastAnniversary,@EndDate)
AS TotalMonths
,(DATEDIFF(DAY,DATEADD(DAY,-(DAY(@EndDate)-1),@EndDate),@EndDate))+1
AS TotalDays
,a.LastAnniversary
FROM
cteAnniversaries a
)
-- NOW GET THE DECIMAL PART AND ADD TOGETHER
SELECT
(CASE
WHEN
dpy.TotalYears
+ CAST(DATEDIFF(DAY,dpy.RecentAnniversary,@EndDate) AS NUMERIC(6,2))
/ ISNULL(CAST(dpy.NumDaysCurrYear AS NUMERIC(6,2)),1) > 0
THEN
dpy.TotalYears
+ CAST(ROUND(CAST(DATEDIFF(DAY,dpy.RecentAnniversary,@EndDate) AS NUMERIC(6,2))
/ ISNULL(CAST(dpy.NumDaysCurrYear AS NUMERIC(6,2)),1),4) AS NUMERIC(6,4))
ELSE 0
END) AS TotalAge
,(CASE
WHEN dpy.TotalYears < 0 THEN 0
ELSE dpy.TotalYears
END) AS Years
,(CASE
WHEN dpy.TotalMonths < 0 THEN 0
ELSE dpy.TotalMonths
END) AS Months
,(CASE
WHEN dpy.TotalDays < 0 THEN 0
ELSE dpy.TotalDays
END) AS Days
FROM
cteDaysPerYear AS dpy
)
GO
Output
<?xml version="1.0" ?>
<RESULTS1>
<RECORD>
<person>Frank</person>
<dob>1990-03-20</dob>
<TotalAge>23.1205</TotalAge>
<Years>23</Years>
<Months>2</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Joey</person>
<dob>2000-12-31</dob>
<TotalAge>13.3370</TotalAge>
<Years>13</Years>
<Months>5</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Sue</person>
<dob>2012-02-06</dob>
<TotalAge>1.2356</TotalAge>
<Years>1</Years>
<Months>3</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Mary</person>
<dob>2013-04-01</dob>
<TotalAge>0.0877</TotalAge>
<Years>0</Years>
<Months>1</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Bil</person>
<dob>2012-05-03</dob>
<TotalAge>1.0000</TotalAge>
<Years>1</Years>
<Months>0</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Bob</person>
<dob>2013-01-01</dob>
<TotalAge>0.3342</TotalAge>
<Years>0</Years>
<Months>4</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Will</person>
<dob>2012-10-01</dob>
<TotalAge>1.5863</TotalAge>
<Years>1</Years>
<Months>7</Months>
<Days>3</Days>
</RECORD>
</RESULTS1>