x
Solved

How to calculate this age criteria?

Posted on 2010-08-19
Medium Priority
901 Views
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..

I have their IDs and date of births..(ID,DOB_DT)
0
Question by:pvsbandi
• 3
• 3
• 2
• +2

LVL 4

Expert Comment

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

Author Comment

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

Dave Ford earned 750 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
``````
0

LVL 4

Assisted Solution

kelle1 earned 750 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

ID: 33478904
Thanks!
0

LVL 46

Expert Comment

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

LVL 4

Expert Comment

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 46

Expert Comment

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

ID: 33489510
0

LVL 4

Expert Comment

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 46

Expert Comment

ID: 33490153

Beer?  Beer?

[/pops]

:)
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.