# Calculate age - Most easiest way

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

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

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

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

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

• 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

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

• 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

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

• 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

• Could the if statement be simplified to be...

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

` set @age = @age - 1`

• 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

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

• 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

• 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

• 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)

• 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.

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

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

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