• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1319

# Calculate age from DOB in Sql Server 2005

I need to calculate age from DOB in one single line.  I found the following by searching the web:

(datediff (month, DOBColumn, GETDATE())/12) as age

However, it only works on months and there is subsequently a small margin of error which I can't really live with since this software is for races with age groups.  I don't want to create a function/SP.
0
corebiz
• 12
• 11
• 3
• +3
3 Solutions

Commented:
Hi corebiz,
how about (datediff (day, DOBColumn, GETDATE())) as age

Gary
0

Billing EngineerCommented:
can you clarify the rule(s) you want to apply.
as you noticed, datediff ( month of 31/12/2008 vs 01/01/2009 returns 1 and not 0...

0

Commented:
corebiz,

That gives the age in days
Gary
0

Commented:
How do you want the result returned? Years, Years & decimal of year, years & months & days.....

Kelvin
0

Author Commented:
6 months = 0 years
10 years and 2 months = 10
10 years and 0 months = 10
0

Author Commented:
age as an integer
0

Author Commented:
(integer in years)
0

Commented:
(datediff (Year, DOBColumn, GETDATE())) as age

Kelvin
0

Commented:

declare @dt datetime
set @dt='02/04/1999'
select (datediff (YEAR, @dt, GETDATE())) as age --result is 10
GO

0

Commented:
this works fine with every data you want.

``````declare @dt datetime
set @dt='02/04/1999'
select (datediff (YEAR, @dt, GETDATE())) as age --result is 10
GO

declare @dt datetime
set @dt='03/04/1999'
select (datediff (YEAR, @dt, GETDATE())) as age --result is 10
GO

declare @dt datetime
set @dt='02/01/2009'
select (datediff (year, @dt, GETDATE())) as age --result is 0
GO

declare @dt datetime
set @dt='02/07/1980'
select (datediff (year, @dt, GETDATE())) as age --result is 29
GO
``````
0

Author Commented:
Not true with the last one, if your birthday is in July, you haven't turned 29 yet.
0

Commented:
it's not July, it's Feb, 07-Feb-1980, isn't it true?
0

Commented:

declare @dt datetime
set @dt='07/07/1980'
select (datediff (month, @dt, GETDATE())/12) as age --result is 28
GO

0

Author Commented:
You're stabbing in the dark - I already said at the top I was using that and it goes wrong on the days.

I'm currently trying:

SELECT     DOB, CONVERT(int, DATEDIFF(day, DOB, GETDATE()) / 365.25) AS age
FROM         tbEntrant

But it has variable results for today, some days on 26th are correct and others not.
0

Commented:
won't it correct?

declare @dt datetime
set @dt='07/07/1980'
select floor(CONVERT(decimal(5,2), datediff (month, @dt, GETDATE()))/12.00) as age
GO

0

Commented:
if you will see below two queries which is using same date, first will show you 29, which is wrong for your calculation  and second one will show you 28 which is right for you I guess.

``````declare @dt datetime
set @dt='09/07/1980'
select (datediff (year, @dt, GETDATE())) as age --result is 29
GO

declare @dt datetime
set @dt='09/07/1980'
select floor(CONVERT(decimal(5,2), datediff (month, @dt, GETDATE()))/12.00) as age --28
GO
``````
0

Commented:
any update? is above post satisfying your needs?
0

Author Commented:
I get an overflow error - did you run the statement?
0

Commented:
yes, I did for sure. are you using dd/mm/yyyy or mm/dd/yyyy?

0

Author Commented:
DateTime is a universal type stored in the database
0

Commented:
if you run below snippet you won't face any problem, I suspect your data. can you please show me few data here, may be past your date column here
``````declare @dt datetime
set @dt='07/07/1980'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO
``````
0

Commented:
see here, I have tried quite a few different date format and it is working with everything as it is, without any problem.
``````declare @dt datetime
set @dt='23 Apr 2009 13:26:57:153'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO

declare @dt datetime
set @dt='2009/04/23'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO

declare @dt datetime
set @dt='Apr 23 1999  1:26PM'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO

declare @dt datetime
set @dt='23 Mar 1950'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO
``````
0

Author Commented:
I'm not sure you understand the problem.  Try 28 May and you will see it doesn't work correctly until June 1.  You're losing a the days between then and the end of the month.
0

Commented:
not getting you 100% even have a look at this.

declare @dt datetime
set @dt='23 May 1950'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --58
GO

declare @dt datetime
set @dt='23 Jun 1950'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --58
GO

0

Commented:
following both are showing 58, what should be come instead?

declare @dt datetime
set @dt='29 May 1950'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --58
GO

declare @dt datetime
set @dt='1 Jun 1950'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --58
GO

0

Commented:
This seems to work in all cases....

declare @dt datetime
set @dt='19500527'
select year(getdate() - @dt -1) - 1900  as age

I believe the reas0n you have to subtract -1 days is because '1900-01-01' = 0 so simply subtracting the base year of 1900 still leaves a one day rounding error.
0

Author Commented:
That's an elegant solution.  Only trouble is that on today (27th May), the age is still at the wrong year, so I took away the -1 to get it correct.

The trouble with both this and Ritesh's solution is that they bomb out if the data is not a typical year (ie. year is 9000), which mine doesn't.  I can't change my customer's data so I need a failproof solution and I'm willing to tolerate the minor errors in my current solution (some of the current days don't work out the year correctly - maybe 365.25 is the wrong year to use?).
0

Commented:
Your rule works for most years but leap years are not as consistent as we'd like to use a constant of 365.25 (that assumes a leap year every 4 years.

The rules for leap years are:

In general terms the algorithm for calculating a leap year is as follows...

A year will be a leap year if it is divisible by 4 but not by 100. If a year is divisible by 4 and by 100, it is not a leap year unless it is also divisible by 400.

Thus years such as 1996, 1992, 1988 and so on are leap years because they are divisible by 4 but not by 100. For century years, the 400 rule is important. Thus, century years 1900, 1800 and 1700 while all still divisible by 4 are also exactly divisible by 100. As they are not further divisible by 400, they are not leap years.

Having supplied that, I'm not even goiung to attempt to dream up a solution to handle this.

Kelvin
0

Author Commented:
When I first looked at solutions for this problem, all the answers came as functions/SP's, simply because of this kind of logic.  I'm just in a position to modify the DB, so I'm willing to go with a less than perfect solution.
0

Author Commented:
Nobody provided a failproof solution, but helped along the way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.