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
13
Medium Priority
?
564 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 60

Accepted Solution

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

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

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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…

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.

Join & Ask a Question