Solved

TSQL - Calculate Age

Posted on 2011-09-04
13
557 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
[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
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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
LVL 60

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 60

Expert Comment

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

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 60

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 74

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

717 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