How to calculate age from date of birth base on start date in SQL query

Transact-SQL
1

I've been trouble with doing query on calculate age base on minimum effective date for each employee. How would I do this? Please help and thank you in advance.
Here's my query and don't know how to get age calculate yet

select a.EMPLOYEE, a.LAST_NAME, a.FIRST_NAME, b.BIRTHDATE, c.StartDate, c.PLAN_CODE,
from EMPLOYEE a
right outer join PAEMPLOYEE b on a. EMPLOYEE = b.EMPLOYEE
right outer join (Select distinct EMPLOYEE, min(START_DATE) as StartDate, PLAN_CODE, SMOKER from BENEFIT
where PLAN_CODE in ('GAP1', 'CI01', 'CI21')
GROUP BY EMPLOYEE, PLAN_CODE, SMOKER)c
on a.EMPLOYEE = c.EMPLOYEE
order by EMPLOYEE, StartDate

Emp# LName FName BirthDate StartDate PlanCode
100 Warman James 1955-11-25 2014-01-01 GAP1
100 Warman James 1955-11-25 2014-01-01 CI21
101 Strickland James 1956-10-08 2015-01-01 CI01
101 Strickland James 1956-10-08 2015-01-01 CI21
AND SO ON...

2

In order to calculate age you can write like this:

SELECT
DATEDIFF(yy,CONVERT(DATETIME,birthdate),stardate) as age

3

This only calculates the years not taking into account when the birthdate is. To get more accurate you can try months
select
DATEDIFF(month,CONVERT(DATETIME,birthdate),stardate)/12 as age

4 
SELECT
    a.EMPLOYEE,
    a.LAST_NAME,
    a.FIRST_NAME,
    b.BIRTHDATE,
    c.StartDate,
    c.PLAN_CODE,
	AgeAsofStartDate = 
	(
	   CAST(CONVERT(CHAR(8), c.StartDate, 112) AS INT)
	   - 
	   CAST(CONVERT(CHAR(8), b.BIRTHDATE, 112) AS INT)
	)/10000
FROM
    EMPLOYEE    
   ---... rest of your code

This will give you the exact age as of StartDate, taking into account all peculiarities of the calendar such as leap year etc.

5

Thanks mike

6

That depends on specifically how you want to treat a leap year.

Does a birthday of Feb 29 mean a person is a year older in non-leap years on Feb 28 or Mar 1? That is the trickiest issue when dealing with age. For some purposes, such as retirement calcs, laws can even require one method or the other.

7

CREATE FUNCTION [dbo].[fnAge]
(
@pDateOfBirth DATETIME,
@pAsOfDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @vAge INT
IF @pDateOfBirth >= @pAsOfDate
RETURN 0
SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)
IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
(MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND
DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @vAge = @vAge - 1
RETURN @vAge
END

9

To calculate age, add this to your select clause: FLOOR(DATEDIFF(DAY, b.BIRTHDATE, GETDATE()) / 365.25) AS Age. This calculates age based on birthdate and current date.