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.
corebizAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Gary
0
Guy Hengel [angelIII / a3]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
tbsgadiCommented:
corebiz,

That gives the age in days
Gary
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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


Kelvin
0
corebizAuthor Commented:
6 months = 0 years
10 years and 2 months = 10
10 years and 0 months = 10
0
corebizAuthor Commented:
age as an integer
0
corebizAuthor Commented:
(integer in years)
0
Kelvin SparksCommented:
(datediff (Year, DOBColumn, GETDATE())) as age

Kelvin
0
RiteshShahCommented:
what you think about this?

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

0
RiteshShahCommented:
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

Open in new window

0
corebizAuthor Commented:
Not true with the last one, if your birthday is in July, you haven't turned 29 yet.
0
RiteshShahCommented:
it's not July, it's Feb, 07-Feb-1980, isn't it true?
0
RiteshShahCommented:
what about this one?

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

0
corebizAuthor 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
RiteshShahCommented:
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
RiteshShahCommented:
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

Open in new window

0
RiteshShahCommented:
any update? is above post satisfying your needs?
0
corebizAuthor Commented:
I get an overflow error - did you run the statement?
0
RiteshShahCommented:
yes, I did for sure. are you using dd/mm/yyyy or mm/dd/yyyy?

0
corebizAuthor Commented:
DateTime is a universal type stored in the database
0
RiteshShahCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RiteshShahCommented:
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

Open in new window

0
corebizAuthor 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
RiteshShahCommented:
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
RiteshShahCommented:
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
folderolCommented:
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
corebizAuthor 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
Kelvin SparksCommented:
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
corebizAuthor 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
corebizAuthor Commented:
Nobody provided a failproof solution, but helped along the way.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.