Converting date of birth to Age

Question

Converting date of birth to Age

Lfitz

SSC Veteran

Points: 203
More actions
November 13, 2006 at 5:01 am

#193016

hi there
I was wondering if somebody could help. I am trying to write script to convert date of birth to the persons age. Then count how many people are between 20-29, 30-39 and so on based on gender. So i should come out with a report like this
Age Male Female
20- 29 5 4
30-39
40-49

Thank you
lisa

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

iBuildDW Mr or Mrs. 500 Points: 587 More actions · Answer 1

One way to do this, you can use DATEDIFF to determine age. Then COUNT using a BETWEEN statement in your WHERE clause to get your result.

What do you have so far?

Lfitz SSC Veteran Points: 203 More actions · Answer 2

At the moment i have this

select round(months_between(sysdate,dob)/12,0) as age

From hr_person

Not much

EddieBaquet SSC Veteran Points: 267 More actions · Answer 3

Hi, Lisa.

Here is a way to do this using the Adventure works database. First, use a common table expression to summarize by age group. This creates a new field, AgeGroup, based on the age of the person. Then count by Gender while grouping by the new AgeGroup.

Don't forget the first semicolon in case this is not the first line of code in your batch.

Hope this helps!

Eddie

;

with

cte

as

(

select

Gender

,

case

when datediff(month, BirthDate, getdate())/12 between 20 and 29 then '20-29'

when datediff(month, BirthDate, getdate())/12 between 30 and 39 then '30-39'

when datediff(month, BirthDate, getdate())/12 between 40 and 49 then '40-49'

when datediff(month, BirthDate, getdate())/12 > 49 then '50+'

end as AgeGroup

from HumanResources.Employee

)

select

AgeGroup

,

count(case gender when 'M' then 1 end) as Male,

count(case gender when 'F' then 1 end) as Female

from

cte

group

by AgeGroup

"If you don't take the time to do it right, when are you going to find the time to do it over?"

Ken McKelvey SSCoach Points: 18842 More actions · Answer 4

There are probably better ways, but something like this should work in both SQL2000 and SQL2005:

SELECT CASE

WHEN D.Age BETWEEN 20 AND 29 THEN '20-29'

WHEN D.Age BETWEEN 30 AND 39 THEN '30-39'

WHEN D.Age BETWEEN 40 AND 49 THEN '40-49'

ELSE 'Outside Range' END as AgeRange

,COUNT(CASE gender WHEN 'M' THEN 1 END) AS Male

,COUNT(CASE gender WHEN 'F' THEN 1 END) AS Female

FROM (

SELECT P.gender

,YEAR(CURRENT_TIMESTAMP) - YEAR(P.dob)

- CASE

WHEN MONTH(CURRENT_TIMESTAMP) > = MONTH(P.dob)

AND DAY(CURRENT_TIMESTAMP) > = DAY(P.dob)

THEN 0

ELSE 1 END AS age

FROM hr_person P ) D

GROUP BY CASE

WHEN D.Age BETWEEN 20 AND 29 THEN '20-29'

WHEN D.Age BETWEEN 30 AND 39 THEN '30-39'

WHEN D.Age BETWEEN 40 AND 49 THEN '40-49'

ELSE 'Outside Range' END

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Ken,

I tried a quick test and I am not seeing your solution work correctly. Please indicate where I am going wrong. I have a select statement in front of your solution to check the values...

Thanks

CREATE TABLE #Age( PersonID integer IDENTITY(1,1),

BirthDate smalldatetime,

Gender varchar(1))

INSERT INTO #Age( BirthDate, Gender)

SELECT '12/31/1946', 'F'

UNION

SELECT '12/31/1956', 'M'

UNION

SELECT '12/31/1957', 'F'

UNION

SELECT '12/31/1960', 'M'

UNION

SELECT '12/31/1966', 'F'

UNION

SELECT '12/31/1970', 'M'

UNION

SELECT '12/31/1976', 'F'

UNION

SELECT '12/31/1980', 'M'

UNION

SELECT '12/31/1986', 'F'

UNION

SELECT '12/31/1990', 'M'

SELECT DATEDIFF( year, BirthDate, GETDATE()) AS [Age], Gender FROM #Age ORDER BY BirthDate, Gender

SELECT CASE

WHEN D.Age BETWEEN 20 AND 29 THEN '20-29'

WHEN D.Age BETWEEN 30 AND 39 THEN '30-39'

WHEN D.Age BETWEEN 40 AND 49 THEN '40-49'

ELSE 'Outside Range'

END as AgeRange,

COUNT(CASE gender WHEN 'M' THEN 1 END) AS Male,

COUNT(CASE gender WHEN 'F' THEN 1 END) AS Female

FROM ( SELECT P.gender, YEAR(CURRENT_TIMESTAMP) - YEAR( P.BirthDate) -

CASE

WHEN MONTH( CURRENT_TIMESTAMP) > = MONTH( P.BirthDate)

AND DAY( CURRENT_TIMESTAMP) > = DAY( P.BirthDate)

THEN 0

ELSE 1

END AS age

FROM #Age P ) D

GROUP BY CASE

WHEN D.Age BETWEEN 20 AND 29 THEN '20-29'

WHEN D.Age BETWEEN 30 AND 39 THEN '30-39'

WHEN D.Age BETWEEN 40 AND 49 THEN '40-49'

ELSE 'Outside Range'

END

DROP TABLE #Age

I wasn't born stupid - I had to study.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 6

Farrell,

I think the difference is that your quick age calculation just uses the difference in years. As all your BirthDates are in December you should really minus one from the difference in years as the Birthday has not yet been reached. This is what the CASE in my age calculation does although, on looking at it again, it would probably be more efficient to start with DATEDIFF instead of using YEAR twice.

SELECT DATEDIFF( year, BirthDate, GETDATE()) AS FarrellAge

,YEAR(CURRENT_TIMESTAMP) - YEAR( BirthDate) -

CASE

WHEN MONTH( CURRENT_TIMESTAMP) > = MONTH( BirthDate)

AND DAY( CURRENT_TIMESTAMP) > = DAY( BirthDate)

THEN 0

ELSE 1

END AS KenAge

,DATEDIFF( year, BirthDate, CURRENT_TIMESTAMP) -

CASE

WHEN MONTH( CURRENT_TIMESTAMP) > = MONTH( BirthDate)

AND DAY( CURRENT_TIMESTAMP) > = DAY( BirthDate)

THEN 0

ELSE 1

END AS NewKenAge1

,DATEDIFF( year, BirthDate, CURRENT_TIMESTAMP) -

CASE

WHEN MONTH( CURRENT_TIMESTAMP) < MONTH( BirthDate)

OR DAY( CURRENT_TIMESTAMP) < DAY( BirthDate)

THEN 1

ELSE 0

END AS NewKenAge2

FROM #Age P

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Thanks bud! I will look at this later today.

I wasn't born stupid - I had to study.

Michael Carey-400169 Mr or Mrs. 500 Points: 512 More actions · Answer 8

Try adding a function like this:

--=========================

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE FUNCTION GetAgeInYears

--mcarey 6/1/2005

--declare input variables:

(@DOB datetime, --DateOfBirth

@AsOfDate datetime) --DateForWhichAge is being Calculated

RETURNS decimal(6,1) -- AgeInYears

AS

BEGIN

DECLARE @AgeInYears as decimal (6,2)

--Patient's age in years at the time of visit or other date given.

Select @AgeInYears = datediff(yy, @DOB, @AsOfDate) -

(CASE WHEN (datepart(m, @DOB) > datepart(m, @AsOfDate))

OR (datepart(m, @DOB) = datepart(m, @AsOfDate)

AND datepart(d, @DOB) >= datepart(d, @AsOfDate))

THEN 1

ELSE 0

END)

RETURN (@AgeInYears)

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

--===================

Then, you can do a select query like this:

Select

sum(case when dbo.GetAgeInYears(X.dob,getdate()) between 10 and 19

then 1 else 0 end) [Ten to 19Count],

sum(case when dbo.GetAgeInYears(X.dob,getdate()) between 20 and 29

then 1 else 0 end) [Twenty to 29Count]

--and so forth

from

YourTable X

--In the select statement, you may or may not have to use the dbo. in front of the GetAgeInYears function, depending on how you set up the function.

From there, it is easy to work in the gender buckets.

Mike

--=========================

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

"SysDate" "Months_Between" Are you trying to do this in Oracle or SQL Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

Dunno about Oracle, but here's a way that works in SQL Server...

-- some data with dob and genders

declare @ages table ( dob char(8) not null, gender char(1) not null )

insert @ages

select '19560101', 'M' union all

select '19460624', 'F' union all

select '19590225', 'M' union all

select '19681112', 'F' union all

select '19500907', 'F' union all

select '19550703', 'M' union all

select '19640211', 'M' union all

select '19490106', 'F' union all

select '19701121', 'F' union all

select '19740922', 'M'

-- with mod as range indikator

-- the purpose of the @mod variable is that it sets the 'gap' in lo-hi ranges.

-- ie 10 years, or 20 or whatever is desired

declare @mod int

set @mod = 10

select age - (age % @mod) as 'lowrange',

(age - (age % @mod)) + @mod - 1 as 'highrange',

sum(male) as 'Male',

sum(female) as 'Female'

from (

select cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as 'Age',

case gender when 'M' then 1 else 0 end as 'Male',

case gender when 'F' then 1 else 0 end as 'Female'

from @ages

) x

group by age - (age % @mod),

(age - (age % @mod)) + @mod - 1

go

lowrange highrange Male Female

----------- ----------- ----------- -----------

30 39 1 2

40 49 2 0

50 59 2 2

60 69 0 1

One could concatenate lowrange and highrange columns into a single columns if desired.

Is it something like this that you want?

/Kenneth

Lowell SSC Guru Points: 323518 More actions · Answer 11

if this is for oracle, here is a function in oracle syntax that returns the age:

CREATE

OR REPLACE FUNCTION GETAGE(PRM_DOB in date)

return

number

AS

begin

return (round(MONTHS_BETWEEN(PRM_DOB, SYSDATE) / 12) - 1) ;

end

GETAGE ;

/

you could use the function, or as you can see, the formula is pretty short and straight forward, so you could use it inline as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ken McKelvey SSCoach Points: 18842 More actions · Answer 12

Kenneth,

Nice use of modulus but your age calculation is wrong.

eg. A child will come out as 1 year old when he/she is one day old.

Looking back in this thread, my age calculation was also wrong!

I think the following is a reliable way of getting the correct age in TSQL:

SELECT DATEDIFF(year, dob, GETDATE())

- CASE

WHEN MONTH(GETDATE()) > MONTH(dob)

THEN 0

WHEN MONTH(GETDATE()) = MONTH(dob) AND DAY(GETDATE()) >= DAY(dob)

THEN 0

ELSE 1 END AS age

Ken

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 13

Ah, yes, well.. that is true.

It is 'sort of' intended, though.. But it's a side effect.

When figuring out age from a dob date, there's one question that needs answering first.

If today is a birthday, should it count as one year older, or should the age be added tomorrow, the day after the birthday?

Depending on the rule, there are different ways to do it.

select cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as 'Age'

select cast(floor(datediff(day, dob, getdate()) / 365.25) as int) as 'Age'

It so happened I pasted the former variant in the example. A side effect of that is that 11 months old is rounded up to 1 year old.

This in the other hand would only be applicale for a dob that actually has a birthday the given day.

The most common way we look at ages is to use the floor variant, so, my bad.

/Kenneth

Ken McKelvey SSCoach Points: 18842 More actions · Answer 14

Floor nearly works but it produces a rounding error every 4 years as below:

dob Birthday FloorAge CaseAge RoundingError

-------- -------- ----------- ----------- -------------

19460624 19460624 0 0 N

19460624 19470624 0 1 Y

19460624 19480624 2 2 N

19460624 19490624 3 3 N

19460624 19500624 4 4 N

19460624 19510624 4 5 Y

19460624 19520624 6 6 N

19460624 19530624 7 7 N

19460624 19540624 8 8 N

19460624 19550624 8 9 Y

19460624 19560624 10 10 N

19460624 19570624 11 11 N

19460624 19580624 12 12 N

19460624 19590624 12 13 Y

19460624 19600624 14 14 N

19460624 19610624 15 15 N

19460624 19620624 16 16 N

19460624 19630624 16 17 Y

19460624 19640624 18 18 N

19460624 19650624 19 19 N

19460624 19660624 20 20 N

19460624 19670624 20 21 Y

19460624 19680624 22 22 N

19460624 19690624 23 23 N

19460624 19700624 24 24 N

19460624 19710624 24 25 Y

19460624 19720624 26 26 N

19460624 19730624 27 27 N

19460624 19740624 28 28 N

19460624 19750624 28 29 Y

19460624 19760624 30 30 N

19460624 19770624 31 31 N

19460624 19780624 32 32 N

19460624 19790624 32 33 Y

19460624 19800624 34 34 N

19460624 19810624 35 35 N

19460624 19820624 36 36 N

19460624 19830624 36 37 Y

19460624 19840624 38 38 N

19460624 19850624 39 39 N

19460624 19860624 40 40 N

19460624 19870624 40 41 Y

19460624 19880624 42 42 N

19460624 19890624 43 43 N

19460624 19900624 44 44 N

19460624 19910624 44 45 Y

19460624 19920624 46 46 N

19460624 19930624 47 47 N

19460624 19940624 48 48 N

19460624 19950624 48 49 Y

19460624 19960624 50 50 N

19460624 19970624 51 51 N

19460624 19980624 52 52 N

19460624 19990624 52 53 Y

19460624 20000624 54 54 N

19460624 20010624 55 55 N

19460624 20020624 56 56 N

19460624 20030624 56 57 Y

19460624 20040624 58 58 N

19460624 20050624 59 59 N

19460624 20060624 60 60 N