In SQL Calculate Date of Birth based on Year

I need to calculate the age of the person based on the how old they were on January 1 of the corresponding YEAR.

My SQL results look something like the following:
``````ID           Year           birthdate
1             2014          1948-04-11 00:00:00.000
2             2012          1960-01-10 00:00:00.000
3             2013          1954-09-28 00:00:00.000
``````

After I have determined the age, I then need to place that person into one of five category age ranges:
(1-17, 18-34, 35-54, 55-74, 75 and up)

Ideally, the results would look something like this:
``````ID           Year           birthdate                                          Age             Age Group
1             2014          1948-04-11 00:00:00.000               65               55-74
2             2012          1960-01-10 00:00:00.000               51               35-54
3             2013          1954-09-28 00:00:00.000               58                55-74
``````
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Programmer AnalystCommented:
``````CREATE function [dbo].[fn_Get_Age]
(@i_BirthDate AS datetime)

RETURNS INT
AS

BEGIN
declare @finalAge int = 0
declare @DOB datetime
Set  @DOB = @i_BirthDate

Select @finalAge =
case
when DATEADD(YY, DATEDIFF(YY, @DOB , GETDATE()),  @DOB )  > GETDATE()
then DATEDIFF(YY, @DOB , GETDATE()) - 1
else DATEDIFF(YY, @DOB , GETDATE())
end

return @finalAge
END
``````
0
Programmer AnalystCommented:
See if you can get that working, then let's put the last column, Age Group together.
0
Senior DBACommented:
SELECT ID, Year, Age, CASE
WHEN Age >= 75 THEN '75+'
WHEN Age >= 55 THEN '55-74'
WHEN Age >= 35 THEN '35-54'
WHEN Age >= 18 THEN '18-34'
ELSE '1-17' END AS [Age Group]
FROM ...
CROSS APPLY (
SELECT DATEDIFF(YEAR, birthdate, CAST(Year AS char(4)) + '0101') -
CASE WHEN DATEPART(DAYOFYEAR, birthdate) = 1 THEN 0 ELSE 1 END AS Age
) AS assign_alias
0
Programmer AnalystCommented:
Well done Scott!
0
Senior DBACommented:
For example:

SELECT ID, Year, Age, CASE
WHEN Age >= 75 THEN '75+'
WHEN Age >= 55 THEN '55-74'
WHEN Age >= 35 THEN '35-54'
WHEN Age >= 18 THEN '18-34'
ELSE '1-17' END AS [Age Group]
FROM (
SELECT 1 AS ID,      2014 AS Year, '1948-04-11 00:00:00.000' AS birthdate UNION ALL
SELECT 2,            2012,         '1960-01-10 00:00:00.000' UNION ALL
SELECT 3,            2013,         '1954-09-28 00:00:00.000' UNION ALL
SELECT 4,            2011,         '2000-12-31 00:00:00.000' UNION ALL
SELECT 4,            2011,         '2001-01-01 00:00:00.000'
) AS test_data
CROSS APPLY (
SELECT DATEDIFF(YEAR, birthdate, CAST(Year AS char(4)) + '0101') -
CASE WHEN DATEPART(DAYOFYEAR, birthdate) = 1 THEN 0 ELSE 1 END AS Age
) AS assign_alias
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
The following is my actual query. I am getting an Invalid column name 'Year' for
CAST(Year AS char(4))

`````` 	SELECT A.AEA_EmployerOrganizationIdName [Employer], A.AEA_titleseason [Title/Season], workweek as [Work Weeks],
YEAR(startdate) as [Year], Region,[Primary Duty], [GROUP],[Contract Type], [Category Name], ISNULL(A.aea_BusinessRepIdName,'Unassigned') [Rep], [Show Name],
aea_actorcontractid [Contract ID], aea_partsandincrementsid [Part ID],
ISNULL(FC.aea_ethniccodename,'Unknown') [Ethnicity], fc.birthdate,
Age, CASE
WHEN Age >= 75 THEN '75+'
WHEN Age >= 55 THEN '55-74'
WHEN Age >= 35 THEN '35-54'
WHEN Age >= 18 THEN '18-34'
ELSE '1-17' END AS [Age Group]

from bpt_aea_workweektemp_actorid
INNER JOIN AEA_employeragreement as A
ON bpt_aea_workweektemp_actorid.aea_employeragreementid = A.AEA_employeragreementId
INNER JOIN [FilteredContact] fc
ON fc.contactid = bpt_aea_workweektemp_actorid.[Actor id]
CROSS APPLY (
SELECT DATEDIFF(YEAR, birthdate, CAST(Year AS char(4)) + '0101') AS Age
) AS assign_alias
``````
0
Programmer AnalystCommented:
You may need to wrap the word "Year" in brackets...like this: [Year]
0
Senior DBACommented:
Sorry, I didn't realize "year" was not the actual column name.  Since you're deriving the name from:

YEAR(startdate) as [Year]

change the CROSS APPLY to:

CROSS APPLY (
SELECT DATEDIFF(YEAR, birthdate, CAST(YEAR(startdate) AS char(4)) + '0101') -
CASE WHEN DATEPART(DAYOFYEAR, birthdate) = 1 THEN 0 ELSE 1 END AS Age
) AS assign_alias
0
Author Commented:
Thanks that worked!!!
0
Senior DBACommented:
You're welcome!

CROSS APPLY is really good for assigning alias names you can actually use anywhere in the rest of the query: SELECT list, WHERE clause, ORDER BY, etc..
0
Data AnalystCommented:

DECLARE @dob  datetime
Declare @Grp varchar(100)
SET @dob='1948-04-11 00:00:00.000'

SELECT CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0))-1 AS Age
,      CASE WHEN dateadd(year, datediff (year, @dob, getdate()), @dob) > 55 or dateadd(year, datediff (year, @dob, getdate()), @dob) < 70
THEN
'55-74'
ELSE '0-0'
END AS [Age Group]
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.