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

Open in new window


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

Open in new window

swaggrKAsked:
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.

David L. HansenProgrammer AnalystCommented:
How about using this function:
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

Open in new window

0
David L. HansenProgrammer AnalystCommented:
See if you can get that working, then let's put the last column, Age Group together.
0
Scott PletcherSenior 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David L. HansenProgrammer AnalystCommented:
Well done Scott!
0
Scott PletcherSenior 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

Your issues matter to us.

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

Start your 7-day free trial
swaggrKAuthor 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 

Open in new window

0
David L. HansenProgrammer AnalystCommented:
You may need to wrap the word "Year" in brackets...like this: [Year]
0
Scott PletcherSenior 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
swaggrKAuthor Commented:
Thanks that worked!!!
0
Scott PletcherSenior 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
Pratik MakwanaData AnalystCommented:
Here is your solution...........

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.