Age

Question

Post reply

Age

QQ-485619

Ten Centuries

Points: 1191
More actions
May 26, 2010 at 8:36 pm

#232882

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

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

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

You can use DATEDIFF function:

LIke:

SET DATEFORMAT DMY

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

WayneS SSC Guru Points: 95409 More actions · Answer 2

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!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

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! 🙂

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

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

mitch 14804 SSC Veteran Points: 298 More actions · Answer 7

Wayne,

This doesn't work on leap years.

Try these dates:

@DOB = '09/26/2011'

GetDate() = '09/25/2012'

mitch

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

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

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

Adam Gojdas (5/28/2010)
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.

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

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.

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

Paul White SSC Guru Points: 150467 More actions · Answer 11

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:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

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.

Sergiy SSC Guru Points: 110206 More actions · Answer 13

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

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 14

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