Calculate age - Most easiest way

Question

Calculate age - Most easiest way

ece.viky

Right there with Babe

Points: 727
More actions
November 17, 2014 at 5:45 pm

#295110

Comments posted to this topic are about the item Calculate age - Most easiest way

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

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

andrew_dale Ten Centuries Points: 1260 More actions · Answer 1

If you only need age in years, then the following is easier as it does not need you to create a function so can be used directly in a select statement. It calculates age by converting the dates to yyyymmdd format, subracting one from the other, diving by 10000 and rounding down.

FLOOR((CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))

- CONVERT(INT,CONVERT(CHAR(8),[dob],112)))/10000)

joseph.connor SSC Journeyman Points: 77 More actions · Answer 2

Neat, although isn't the Floor function obsolete, since you're performing an Integer Division?

dave.ott 20779 Grasshopper Points: 20 More actions · Answer 3

I've always liked this to get the age in years

select cast(datediff(d,@dob,getdate())/365.25 as int)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

andrew_dale (12/2/2014)
If you only need age in years, then the following is easier as it does not need you to create a function so can be used directly in a select statement. It calculates age by converting the dates to yyyymmdd format, subracting one from the other, diving by 10000 and rounding down.
FLOOR((CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))
- CONVERT(INT,CONVERT(CHAR(8),[dob],112)))/10000)

A person born on 2012-02-29 should be how old on 2014-02-28?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

dave.ott 20779 (12/2/2014)
I've always liked this to get the age in years
select cast(datediff(d,@dob,getdate())/365.25 as int)

Try any date pair (DOB and reference date) where the month and the day are the same, such as

2012-03-02 and2014-03-02.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

andrew_dale Ten Centuries Points: 1260 More actions · Answer 6

A person born on 2012-02-29 should be how old on 2014-02-28?

1 year old still, the day has not rolled over to 1st March

select '20120228' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120228'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120228'))/10000)

union all

select '20120229' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120229'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120229'))/10000)

union all

select '20120301' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120301'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120301'))/10000)

2012022822

2012022912

2012030112

Brymen SSC-Addicted Points: 495 More actions · Answer 7

Could the if statement be simplified to be...

if datediff(dd,dateadd(yy,@age,@dob),@getdate) < 0

set @age = @age - 1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

andrew_dale (12/2/2014)
A person born on 2012-02-29 should be how old on 2014-02-28?
1 year old still, the day has not rolled over to 1st March
select '20120228' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120228'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120228'))/10000)
union all
select '20120229' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120229'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120229'))/10000)
union all
select '20120301' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120301'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120301'))/10000)
2012022822
2012022912
2012030112

Most cultures celebrate the birthday of a "Leaper" on the 28th February, so a Leaper born on 2012-02-29 would celebrate their first birthday on 2013-02-28 and their second on 2014-02-28.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

andrew_dale Ten Centuries Points: 1260 More actions · Answer 9

Most cultures celebrate the birthday of a "Leaper" on the 28th February, so a Leaper born on 2012-02-29 would celebrate their first birthday on 2013-02-28 and their
second on 2014-02-28.

its the law in the UK that any person who was born on 29 February in a leap year will attain the age of 18 on 1 March in a non-leap year for the purpose of voting

http://www.electoralcommission.org.uk/__data/assets/pdf_file/0007/43954/Part-B-Entitlement-to-register-March-2010.pdf

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 10

dave.ott 20779 (12/2/2014)
I've always liked this to get the age in years
select cast(datediff(d,@dob,getdate())/365.25 as int)

This has problems on birthdays. As listed, the code returns 0 when dob = '12/2/2013' and getdate() = '12/2/2014'

Don Simpson

I'm not sure about Heisenberg.

dave.ott 20779 Grasshopper Points: 20 More actions · Answer 11

Yes there is an issue if the age is under 4 years.

I have used the prior version for adults and it works great, but for handling all birthdays

select cast(datediff(d,@dob,getdate())/case when datediff(yy,@dob,getdate())<4 then 365 else 365.25 end as int)

andrew_dale Ten Centuries Points: 1260 More actions · Answer 12

joseph.connor (12/2/2014)
Neat, although isn't the Floor function obsolete, since you're performing an Integer Division?

well spotted, I was guarding against it rounding up where fractional part was > 0.5, but integer division always truncates so you are right that the floor is not needed

http://msdn.microsoft.com/en-us/library/ms175009.aspx

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

ece.viky Right there with Babe Points: 727 More actions · Answer 13

ece.viky

Right there with Babe

Points: 727

January 8, 2015 at 3:39 am

#1768725

Great....Thanks for giving the nice input.....

christopherfox SSC Rookie Points: 30 More actions · Answer 14

I find this works better than a scalar function...

CREATE FUNCTION dbo.AgeInYears

(

@BirthDate DATETIME2,

@AsOfDate DATETIME2

)

RETURNS TABLE

AS

RETURN

(

SELECT DATEDIFF(YEAR , @BirthDate , @AsOfDate) -

CASE WHEN DATEPART(DY, @AsOfDate) < DATEPART(DY, @BirthDate) THEN 1

ELSE 0

END AS Age

);

Then a simple CROSS APPLY in the query...

SELECT I.Name, I.Birthdate, A.Age

FROM Individual AS I

CROSS APPLY AgeInYears(I.Birthdate, CURRENT_TIMESTAMP) AS A

WHERE A.Age >= 18;