# Age

• I have a table called member which contains memberid and DOB. How can I calculate age to date based on Date of Birth.

Thanks,

• You can use DATEDIFF function:

LIke:

`SET DATEFORMAT DMY`

`SELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE`

• ColdCoffee (5/26/2010)

You can use DATEDIFF function:

LIke:

`SET DATEFORMAT DMY`

`SELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE`

Since DateDiff returns the number of boundaries crossed in comparing these two dates, it will report the wrong age if today's date is not >= your DOB. So, use this instead:

`SELECT CASE WHEN datepart(dayofyear, GetDate() ) >= datepart(dayofyear, @DOB)`

` THEN DATEDIFF(yy,@DOB,GETDATE())`

` ELSE DATEDIFF(yy,@DOB,GETDATE()) -1`

` END AGE`

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

• WayneS (5/26/2010)

ColdCoffee (5/26/2010)

You can use DATEDIFF function:

LIke:

`SET DATEFORMAT DMY`

`SELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE`

Since DateDiff returns the number of boundaries crossed in comparing these two dates, it will report the wrong age if today's date is not >= your DOB. So, use this instead:

`SELECT CASE WHEN datepart(dayofyear, GetDate() ) >= datepart(dayofyear, @DOB)`

` THEN DATEDIFF(yy,@DOB,GETDATE())`

` ELSE DATEDIFF(yy,@DOB,GETDATE()) -1`

` END AGE`

That perfectly makes sense!

How sweet it is to start the day with learning a titbit.. wow

Thanks Wayne Shef! 🙂

• Another way to calculate the age is:

`SELECT (ABS(CAST(CONVERT(char(8),GETDATE(),112) AS int)`

` - CAST(CONVERT(char(8),@DOB,112) AS int)) / 10000) [AGE];`

Here is a link to where I originally saw this type of calculation. It is in the comments section. :

http://www.sqlmag.com/article/tsql3/datetime-calculations-part-5.aspx

• You can also check out this article[/url] and the discussion that followed.

• I'd second Lynn's article. It's a good discussion of this topic.

• Wayne,

This doesn't work on leap years.

Try these dates:

@DOB = '09/26/2011'

GetDate() = '09/25/2012'

mitch

• You can use the function on the link below to find the age:

Age Function F_AGE_IN_YEARS

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Another way to calculate the age is:

`SELECT (ABS(CAST(CONVERT(char(8),GETDATE(),112) AS int)`

` - CAST(CONVERT(char(8),@DOB,112) AS int)) / 10000) [AGE];`

Here is a link to where I originally saw this type of calculation. It is in the comments section. :

http://www.sqlmag.com/article/tsql3/datetime-calculations-part-5.aspx

Careful now... Because of the date conversions to the CHAR datatype, that will be relatively slow.

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Michael Valentine Jones (9/25/2012)

You can use the function on the link below to find the age:

Age Function F_AGE_IN_YEARS

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Any chance of making it NOT a scalar function?

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Jeff Moden (9/25/2012)

Careful now... Because of the date conversions to the CHAR datatype, that will be relatively slow.

It is beautiful though - this has long been a favourite of mine (I think it was Rob Farley that first showed it to me).

As far as performance is concerned, well yes it will be slow. But then, all T-SQL solutions will be slow, right? 😉

Just kidding (mostly) - it's a good point that conversion to string is particularly bad. And especially scalar T-SQL UDFs :sick:

• Jeff Moden (9/25/2012)

Michael Valentine Jones (9/25/2012)

You can use the function on the link below to find the age:

Age Function F_AGE_IN_YEARS

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Any chance of making it NOT a scalar function?

I originally wrote this for SQL 2000, so be my guest. 🙂 The code could be greatly simplified too, probably enough to convert it to inline code.

I posted it mainly to give them an example of some code that I know I tested fairly completely.

• Jeff Moden (9/25/2012)

Michael Valentine Jones (9/25/2012)

You can use the function on the link below to find the age:

Age Function F_AGE_IN_YEARS

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Any chance of making it NOT a scalar function?

`SELECT DATEDIFF(yy, 0, GETDATE() - DOB) Age_Way1,`

` YEAR(GETDATE()-DOB) -1900 Age_Way2 `

_____________
Code for TallyGenerator

• My two bits...

declare @dob datetime

set @dob = '2/29/2000'

select convert(char(10),@dob,101) as DOB,

case

when ((MONTH(@dob) * 100) + DAY(@dob)) > ((MONTH(getdate()) * 100) + DAY(getdate()))

then DATEDIFF(year,@dob,getdate()) - 1

else DATEDIFF(year,@dob,getdate()) End as AgeInYears

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