[Mysql] Selecting a date range

Unexpected results

[rubenski]

Hi everyone,

I am working on my first web application and I am having some difficulties selecting a date range from Mysql.

I have two tables containing user information that I want to search from the website. The search form contains a list menu to select between man or woman and it contains two fields for specifying an age range.

Let's say the user filled out that he is looking for a woman between 18 and 25 years old. This is the SQL I use to get it:

SELECT t1.custnr, t1.name, t2.gender
FROM klantnaw AS t1, klantspecs as t2
WHERE geslacht = 'woman'
AND DATE_SUB(DATE_ADD(CURDATE(), INTERVAL -18 YEAR), INTERVAL -7 YEAR) <= t2.birthDate
AND t1.custnr = t2.custnr

The 7 is the difference between the first and the second age. This doesn't work. It gives some results, but wrong results.

Could you tell me how to select an age range from a table that contains birthDates?

[henry0]

Why not doing something like checking what's in between this year month day and year month day

for example
where birth >= '1970-12-21' AND birth < '1977-12-21';

in your DB the date is supposed to use that format:
2005-04-02

Regards

<edit> Typo </edit>

[rubenski]

All I have are two ages submitted through a form. If I would use the method you suggest I'd have to convert the two ages to birthdates before I can use them in the query. I don't know how to do that in Java, the language I use.

In the Mysql manual I found some more examples with date calculations (Google for "3.3.4.5 Date Calculations"), but they are getting me nowhere.

(YEAR(CURRENT_DATE)-YEAR(birhDate)) - (RIGHT(CURRENT_DATE,5)<RIGHT(birthDate,5)) AS age

returns null for each age.

[killroy]

Also, if us DATE_SUB -18 years your actually adding. use DATE_SUB with positive 18 YEARS to SUBTRACT 18 years.

SN