In SQL Server:
You can use the built-in datediff function and compute it in years, however, you will run in to problems as it will round-up.
For example:
DECLARE @Birthdate DATE = '1920-08-31'
DECLARE @AgeasOfDate DATE = '2011-03-15'
SELECT DATEDIFF(yy,@Birthdate, @AgeasOFDate)
results in 91.
The person has not turned 91 yet. To address this we can calculate it as:
SELECT DATEDIFF(mm,@Birthdate, @AgeasOFDate)/12
This results in 90.
This is not completely accurate either... consider the example:
DECLARE @Birthdate DATE = '1920-08-31'
DECLARE @AgeasOfDate DATE = '2011-08-15'
SELECT DATEDIFF(mm,@Birthdate, @AgeasOFDate)/12
results in 91, which is not correct. The person is still 90 years of age for another 16 days.
To address this, use the following:
SELECT CASE WHEN DATEPART(day, @Birthdate) > DATEPART(day, @AgeAsOfDate)
THEN DATEDIFF(month, @Birthdate, @AgeAsOfDate) - 1
ELSE DATEDIFF(month, @Birthdate, @AgeAsOfDate)
END / 12
In SSIS:
Setup a derived column transformation and use the following:
YEAR(AgeAsOfDate) - YEAR(BirthDate) - (SIGN(DATEPART("dy",BirthDate) - DATEPART("dy",AgeAsOfDate)) == 1 ? 1 : 0)
