Date Comparison issue

[EchoFool]

Hey

 

 

I have a WHERE clause which has this:

 

 

<?php
$hi = 99;
$low = 18;


//WHERE clause
age BETWEEN (DATE_ADD(CURDATE(), INTERVAL $low YEAR) AND DATE_ADD(CURDATE(), INTERVAL $hi YEAR)) 
?>

 

 

The field age is in the structure of YYYY-MM-DD

 

Now i have in the database 1 user with this:

 

Age = 1978-05-29

 

 

But my query returns 0 results. The WHERE clause is suppose to find all users whose age is between the two values.

 

 

Can any one see the mistake i am making here?

[Pikachu2000]

You're adding 18 years and 60 years to the current date then seeing if a date in 1978 is between them. I can assure you, it is not.

[EchoFool]

Oops my bad, it was meant to be 18 and 99 not 60 =/

 

 

Do you mean i have to convert "age" from date to years old first.. how can i do that in MySQL =/

[Pikachu2000]

What do you think the DATE_ADD() function in MySQL does? You are adding 18 years to the current date, and you are adding 60 years to the current date, then checking whether a date 33 years in the past is between them. What you need is to calculate the user's age, then see if that number is between 18 and 60.

 

See this post on the MySQL forums for a good way to calculate age.

[EchoFool]

I shall take a look at that thanks! p.s is there a way to not take into account leap years?

 

 

I originally got the DATE_ADD() from here http://www.phpfreaks.com/forums/index.php?topic=345577

[Pikachu2000]

I suppose you could also use DATE_SUB() instead of DATE_ADD() with those same values, and see if the date of birth is between those dates. That would probably work too, but I haven't tested it myself.

[EchoFool]

The one you provided me is a little confusing.

 

((date_format(now(),'%Y') - date_format(ma.DOB,'%Y')) - (date_format(now(),'00-%m-%d') < date_format(ma.DOB,'00-%m-%d'))) AS age 

 

How come it does 3 subtractions then compares if its less than something when calculating the age?

[Pikachu2000]

That's to determine if the birthday has passed or not in the current year. If it hasn't, the age is one less than the value of current year - birth year.

[EchoFool]

Oh i see, thanks for the advice