# TSQL - Calculate Age

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® is a registered trademark of EXPERTS EXCHANGE®

Commented:
TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE

Commented:
which db engine are you using , date functions are dependent in that ....
CEO

Commented:
i am usimg sql server 2008
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Check out ScottPletcher's answer here: http://www.experts-exchange.com/Q_23808219.html
CEO

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.
Chief Technology Officer
Most Valuable Expert 2011

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

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

Chief 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
Snr. Development Manager

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

will give a more accurate result.
CEO

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
Snr. 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')
``````datediff(year,DOB,getdate())-case when dateadd(year, datediff(year,DOB,getdate()), DOB)>getdate() then 1 else 0 end