• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 899
  • Last Modified:

How to calculate this age criteria?

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
pvsbandi
Asked:
pvsbandi
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
kelle1Commented:
SELECT * From tablename WHERE DOB_DT Between '1995-10-01' AND '1996-09-30'
0
 
pvsbandiAuthor Commented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
kelle1Commented:
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
 
pvsbandiAuthor Commented:
Thanks!
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
gmarinoCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
pvsbandiAuthor Commented:
Thanks for your valuable inputs!
0
 
gmarinoCommented:
Kent - sounds like a story in need of a beer (or maybe a bar).  

Good to work with you here!

- Greg
0
 
Kent OlsenData Warehouse Architect / DBACommented:
[pops head up]

Beer?  Beer?

[/pops]


  :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now