Calculate age from DOB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate age from DOB

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-30 : 12:02:51

Hello,

I have a date of birth column in my table and I want to calculate age based on this date. Here is the code I am trying to use in my select statement.

DateDiff ("yyyy",(a.date_of_birth),CurrentDate)

Apparently CurrentDate is not recognized by SQL. What is the best way to do this?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-05-30 : 12:04:18

I'm a beginner and all, but wouldn't it be Getdate()?

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-30 : 12:05:41

Thats so funny I just went to post back that it was GetDate(). I was thinking crystal syntax! Oh well thanks :)

quote:
Originally posted by DavidChel

I'm a beginner and all, but wouldn't it be Getdate()?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-05-30 : 12:18:26

Did you just delete your other topic? I was about to go 2 for 2.

Oh, and incidentally I use Crystal too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 12:42:38

quote:
Originally posted by sross81

Hello,

I have a date of birth column in my table and I want to calculate age based on this date. Here is the code I am trying to use in my select statement.

DateDiff ("yyyy",(a.date_of_birth),CurrentDate)

Apparently CurrentDate is not recognized by SQL. What is the best way to do this?

This wont give you correct date always. What if month of birth was Oct 2000. Taking year alone his age will be 8 whereas he has actually completed only 7 and will get to 8 only by Oct 2008.

I think you need to check month also

SELECT
CASE WHEN DateDiff (mm,(a.date_of_birth),GETDATE())<0 OR 
      (DateDiff (mm,(a.date_of_birth),GETDATE())=0 AND 
       DateDiff (dd,(a.date_of_birth),GETDATE())<0
     THEN DateDiff (yyyy,(a.date_of_birth),CurrentDate) -1
     ELSE DateDiff (yyyy,(a.date_of_birth),CurrentDate)
END
AS Age,..

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-30 : 13:45:27

Hi I found this,

DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME

SELECT @CurrentDate = '20080530', @BirthDate = '19750917'

SELECT DATEDIFF(YY, @BirthDate, @CurrentDate) - CASE WHEN( (MONTH(@BirthDate)*100 + DAY(@BirthDate)) > (MONTH(@CurrentDate)*100 + DAY(@CurrentDate)) ) THEN 1 ELSE 0 END
Maybe it hepls.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-30 : 16:59:12

Many ways to obtain the same results:

DECLARE @Birthdate as DATETIME
Set @Birthdate = '19751201'
select (CONVERT(char,GETDATE(),112)*1-CONVERT(char,@Birthdate,112))/10000

Terry

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-30 : 18:57:12

You can just use the function on the link below.

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

The logic for leap years throws off many solutions. For example, make sure it will return the correct solution of 2 for the following dates:
BirthDate = 2004-02-29
CurrentDate = 2006-02-28

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-30 : 23:57:57

MVJ, I could be wrong but I thought that people born on February 29th during leap years count their age according to the day after February 28th which is either February 29th or March 1st.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-01 : 14:26:10

quote:
Originally posted by tkizer

MVJ, I could be wrong but I thought that people born on February 29th during leap years count their age according to the day after February 28th which is either February 29th or March 1st.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

It actually varies. For legal purposes, some governments say Feb 28 and some say Mar 1. Of course, a particular organization would want to follow the rule that makes the most sense to it.

I really just wanted to emphasize that they need to give it some thought and make sure it is covered by whatever algorithm they use.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources