TSQL - Calculate Age

soozh
soozh used Ask the Experts™
on
How do you calculate someones age in years given thier date of birth (@DOB) and a specific date (@Now).

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE
which db engine are you using , date functions are dependent in that ....
soozhCEO

Author

Commented:
i am usimg sql server 2008
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Chief Technology Officer
Most Valuable Expert 2011
Commented:
Check out ScottPletcher's answer here: http://www.experts-exchange.com/Q_23808219.html
soozhCEO

Author

Commented:
THanks.  I dont think Boon86's solution works... you cant just subtract the years....

For example 1959-Mar-03 to 1960-Jan-01  would give 1 year but infact i would not be one year old until Mar 6th in 1960.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Exactly. Scott's solution accounts for that using a CASE statement to check for that condition.
soozhCEO

Author

Commented:
what is wrong wth just subtracting the years and then in the now mointh and now day are less than then month and then day subtract one year.

soimething like...

years = now_year - then_year

if (now_month < then_month) and (now_day < then_day) then
  years = years -1


Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Are you wanting multi-line SQL or singular statement. Remember MS SQL does not have an IF in inline SQL. It uses CASE. If is used in multi-line statements where you could set a variable for years and then decrement it by one if you meet if condition, but WHY when you can do it all at once using CASE.
Most Valuable Expert 2011
Top Expert 2012
Commented:
>>> what is wrong ....


nothing,  it might look something like this

datediff(yyyy,DOB,getdate()) - case when datepart(dy,getdate()) >= dateparty(dy,DOB) then 0 else 1 end
G Trurab KhanSnr. Development Manager

Commented:
datediff(year,DOB,getdate())-case when dateadd(year, datediff(year,DOB,getdate()), DOB)>=getdate() then 1 else 0 end

Open in new window


will give a more accurate result.
soozhCEO

Author

Commented:
well we are all wrong because neither my algorythm or the TSQL above does not consider leap years.  This is my solution for a function.

ALTER FUNCTION [dbo].[AgeAtOperation]
(
      -- Add the parameters for the function here
      @Personnr varchar(15),
      @OperationDate date
)
RETURNS int
AS
BEGIN
      
 declare @DoB date ;
 declare @Age int ;
 
 set @DoB = dbo.DoB( @Personnr );
 set @Age = datediff(yyyy, @DOB, @OperationDate ) ;
 
 if (datepart(m,@OperationDate) < datepart(m, @DOB))
 or ((datepart(m,@OperationDate) = datepart(m, @DOB)) and (datepart(d,@OperationDate) < datepart(d, @DOB)))
   set @Age = @Age -1 ;
   
 return @Age;


END
G Trurab KhanSnr. Development Manager

Commented:
The date functions in SQL like DATEADD & DATEDIFF do take care of leap years & you can confirm this by giving this command.
select dateadd(year, 1, '29-feb-1968')
     , dateadd(year, 2, '29-feb-1968')
     , dateadd(year, 3, '29-feb-1968')
     , dateadd(year, 4, '29-feb-1968')
     , dateadd(year, 5, '29-feb-1968')
     , dateadd(year, 6, '29-feb-1968')
     , dateadd(year, 7, '29-feb-1968')
     , dateadd(year, 8, '29-feb-1968')
     , dateadd(year, 9, '29-feb-1968')

Open in new window



As per my previous query posted simply change the comparison from ">=" to ">" & you will get the correct result.
 
datediff(year,DOB,getdate())-case when dateadd(year, datediff(year,DOB,getdate()), DOB)>getdate() then 1 else 0 end

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial