Solved

How to calculate this age criteria?

Posted on 2010-08-19
11
893 Views
Last Modified: 2012-06-27
Hi,
  It probably is easy, but i can't seem to figure out how..

  I need to get all kids who turned 15 between october 1st 2010 and september 30th 2011..
  Can someone please help?

I have their IDs and date of births..(ID,DOB_DT)
0
Comment
Question by:pvsbandi
[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
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 4

Expert Comment

by:kelle1
ID: 33478525
SELECT * From tablename WHERE DOB_DT Between '1995-10-01' AND '1996-09-30'
0
 

Author Comment

by:pvsbandi
ID: 33478557
ok,that gets started..
   Can you please tell me how to add 15 years to the DOB,instead of subtracting from the right hands?
0
 
LVL 18

Accepted Solution

by:
Dave Ford earned 250 total points
ID: 33478629

I think the following will work for you...

HTH,
DaveSlash

where  birthdate is not null                    
  and  year(date('2010-09-30') - birthdate) = 14
  and  year(date('2011-09-30') - birthdate) = 15

Open in new window

0
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
LVL 4

Assisted Solution

by:kelle1
kelle1 earned 250 total points
ID: 33478739
In SQL you would use
SELECT * From tablename WHERE DateAdd(yy,15,DOB_DT) Between '2010-10-01' AND '2011-09-30'

In theory, I believe DB2 would return same results by:
SELECT * From tablename WHERE (DOB_DT + 15 Years) Between '2010-10-01' AND '2011-09-30'

0
 

Author Closing Comment

by:pvsbandi
ID: 33478904
Thanks!
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33479391
Late to the party, but I think that I'd write it as:

Kent

SELECT *
FROM mytable
WHERE birthdate between date ('2010-01-01') - 15 years
                    and date ('2011-09-30') - 15 years

Open in new window

0
 
LVL 4

Expert Comment

by:gmarino
ID: 33481152
Kent and kelle1 had the CORRECT solution - from a PERFORMANCE standpoint.  

1) Never use a function on a column (i.e. DateAdd(yy,15,DOB_DT) )
...and
2) Never put a column in an expression (DOB_DT + 15 Years)

...in a where clause when the same can be written in a different format - such as those solutions suggested by Kent and kelle1.

As soon as you use a function against a column, DB2 can no longer use an index that contains that column to improve the performance of the query.  

ALWAY use the function or expression against the constant - which in this case was '2010-10-01'

That's just my 2 scents (pun intended...)

-Greg
a.k.a.  THE Performance Bigot
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33488685
Hi Greg,

Good to see someone else that thinks in terms of performance, not necessarily the prettiest.

One day I'll bore you with stories about building operating systems where one would look for ways to literally cut microseconds from an O/S function.  :)


Kent
0
 

Author Comment

by:pvsbandi
ID: 33489510
Thanks for your valuable inputs!
0
 
LVL 4

Expert Comment

by:gmarino
ID: 33489899
Kent - sounds like a story in need of a beer (or maybe a bar).  

Good to work with you here!

- Greg
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33490153
[pops head up]

Beer?  Beer?

[/pops]


  :)
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
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…

690 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