Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# TSQL - Calculate Age

Posted on 2011-09-04
Medium Priority
564 Views
How do you calculate someones age in years given thier date of birth (@DOB) and a specific date (@Now).

Thanks!
0
Question by:soozh
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4
• 3
• 2
• +3

LVL 7

Expert Comment

ID: 36480532
TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE
0

LVL 19

Expert Comment

ID: 36480554
which db engine are you using , date functions are dependent in that ....
0

Author Comment

ID: 36480633
i am usimg sql server 2008
0

LVL 60

Accepted Solution

Kevin Cross earned 1000 total points
ID: 36480672
Check out ScottPletcher's answer here: http://www.experts-exchange.com/Q_23808219.html
0

Author Comment

ID: 36480678
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

LVL 60

Expert Comment

ID: 36480681
Exactly. Scott's solution accounts for that using a CASE statement to check for that condition.
0

Author Comment

ID: 36480750
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

LVL 60

Expert Comment

ID: 36480759
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

LVL 74

Assisted Solution

sdstuber earned 1000 total points
ID: 36481870
>>> 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

LVL 8

Expert Comment

ID: 36482536
``````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.
0

Author Comment

ID: 36501109
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

LVL 8

Expert Comment

ID: 36502047
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')
``````

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
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.â€‹
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
Weâ€™ve all felt that sense of false security beforeâ€”locking down external access to a database or component and feeling like weâ€™ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many wâ€¦
###### Suggested Courses
Course of the Month11 days, 10 hours left to enroll

#### 636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.