TSQL - Calculate Age

How do you calculate someones age in years given thier date of birth (@DOB) and a specific date (@Now).

Thanks!
soozhAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Check out ScottPletcher's answer here: http://www.experts-exchange.com/Q_23808219.html
0
 
boon86Commented:
TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE
0
 
elimesikaCommented:
which db engine are you using , date functions are dependent in that ....
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
soozhAuthor Commented:
i am usimg sql server 2008
0
 
soozhAuthor 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.
0
 
Kevin CrossChief Technology OfficerCommented:
Exactly. Scott's solution accounts for that using a CASE statement to check for that condition.
0
 
soozhAuthor 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


0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
sdstuberConnect With a Mentor 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
0
 
GhunaimaCommented:
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.
0
 
soozhAuthor 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
0
 
GhunaimaCommented:
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

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.

All Courses

From novice to tech pro — start learning today.