onecaring (10/12/2011)
/*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;
Interesting, if I set @StartDate = '1/1/2009' and @EndDate = '1/1/2010', I get 0001130, not one year. setting @EndDate = '1/2/2010' I get 0010001.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."