Solved

# In SQL Calculate Date of Birth based on Year

Posted on 2014-08-26
121 Views
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
``````
0
Question by:swaggrK

LVL 15

Expert Comment

``````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

LVL 15

Expert Comment

See if you can get that working, then let's put the last column, Age Group together.
0

LVL 68

Expert Comment

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

LVL 15

Expert Comment

Well done Scott!
0

LVL 68

Accepted Solution

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

Author Comment

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

LVL 15

Expert Comment

You may need to wrap the word "Year" in brackets...like this: [Year]
0

LVL 68

Expert Comment

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 Comment

Thanks that worked!!!
0

LVL 68

Expert Comment

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

LVL 2

Expert Comment

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

## Featured Post

### Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.