Select age between two date format

[gonny]

Hi all,

I'm trying to show a query result selecting two type of age from a search form:
es:
input1= min_age = 18
input2= max_age = 50

Now, in database the birthdays are stored in this format "dd-mm-yy" (23-9-1990).
Select bday from profile where bday BETWEEN '$min_age' AND '$max_age'

How can convert bday in age?

[LifeinAsia]

First, you need to computer the dates for birthdays 18 years ago and 50 years ago, then use those dates for your BETWEEN parameters.

Exactly how you do it depends on your specific DB of application language, bus basically:
min_age_date = today - 18 years
max_age_date = today - 50 years
(If using MS SQL, you could use DATEADD(year,-18,getdate()) to compute min_age_date)

Then your SQL query would be:
SELECT bday
FROM profile
WHERE bday BETWEEN max_age_date AND min_age_date

Oh, and if you're not storing it as a DATETIME field, you'll need to do some more work to parse those values- much easier to just convert all the datetime data to a proper DATETIME field.

[edited by: LifeinAsia at 11:46 pm (utc) on Jan. 7, 2009]

[coopster]

It would be much easier if you stored that date in the more widely acceptable ISO format yyyy-mm-dd. But, as it is, you will have to parse your date a bit more to get the calculation described in this thread regarding how to Calculate age [webmasterworld.com] to work.

[gonny]

Database is MySQL and language PHP.

[gonny]

Now I have convert birthdays in yy-mm-dd but in query below I give a sql error:

SELECT id, title, birthday, id_cat, CURDATE(), (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5)<RIGHT(birthday,5)) AS age FROM profile WHERE age BETWEEN '$min_age' AND '$max_age'

Error:"Error: Unknown column 'age' in 'where clause' IP Address: 217.*.*.* mySQL query error: SELECT id, title, birthday, id_cat, CURDATE(), (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5)"

[coopster]

From SELECT Syntax [dev.mysql.com]

It is not allowable to refer to a column alias in a

WHERE

clause, because the column value might not yet be determined when the

WHERE

clause is executed. See Section B.1.5.4, “Problems with Column Aliases” [dev.mysql.com].