Solved

TSQL - Calculate Age

Posted on 2011-09-04
13
518 Views
Last Modified: 2012-05-12
How do you calculate someones age in years given thier date of birth (@DOB) and a specific date (@Now).

Thanks!
0
Comment
Question by:soozh
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 7

Expert Comment

by:boon86
ID: 36480532
TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE
0
 
LVL 19

Expert Comment

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

Author Comment

by:soozh
ID: 36480633
i am usimg sql server 2008
0
 
LVL 59

Accepted Solution

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

Author Comment

by:soozh
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 59

Expert Comment

by:Kevin Cross
ID: 36480681
Exactly. Scott's solution accounts for that using a CASE statement to check for that condition.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:soozh
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 59

Expert Comment

by:Kevin Cross
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 73

Assisted Solution

by:sdstuber
sdstuber earned 250 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

by:Ghunaima
ID: 36482536
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
 

Author Comment

by:soozh
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

by:Ghunaima
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')

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now