/*

Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)

average days in a year: 365.2425

average days ion a month: 30.436875

USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')

RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS

*/

CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDatedatetime = NULL, @EndDatedatetime = NULL)

RETURNS varchar(7)

WITH EXECUTE AS CALLER

AS

BEGIN

RETURN (

RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+

RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) +

RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2)

)

END;