Calculating age of a person

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)