Create this scalar function to get the exact Age.
ALTER FUNCTION GetAgeByBirthDate(
@BirthDate DATETIME,
@CurrentDate DATE
)
RETURNS VARCHAR(200)
BEGIN
DECLARE @Year INT,
@Month INT,
@Day INT,
@Age VARCHAR(200)
IF MONTH(@BirthDate) <> MONTH(@CurrentDate) BEGIN
SELECT @Year = DATEDIFF(yy,@BirthDate,@CurrentDate)-1
SELECT @Month = DATEDIFF(mm,DATEADD(yy,@Year,@BirthDate),@CurrentDate)
END ELSE BEGIN
SELECT @Year = DATEDIFF(yy,@BirthDate,@CurrentDate)
SELECT @Month = 0
END
IF DAY(@BirthDate) <= DAY(@CurrentDate) BEGIN
SELECT @Day = DATEDIFF(dd,DATEADD(mm,@Month,DATEADD(yy,@year,@BirthDate)),@CurrentDate)
END ELSE BEGIN
SELECT @Day = DATEDIFF(dd,DATEADD(mm,@Month-1,DATEADD(yy,@year,@BirthDate)),@CurrentDate)
END
SELECT @Age = 'Your age is ' + CAST(@Year AS VARCHAR(4)) + ' YEARS ' + CAST(@Month AS VARCHAR(4)) + ' MONTHS and ' + CAST(@Day AS VARCHAR(4)) + ' DAYS.'
RETURN @Age
END
After creating this function run this query :
SELECT dbo.GetAgeByBirthDate('1981-12-19',GETDATE())
First argument is the birth date and second is the current date.
