select date between a range

[starphp]

Hello,

 

I have stored date (birth date in date format).. to find records in between age range, I wrote the following:

 

SELECT * FROM users WHERE brith_date BETWEEN (DATE_SUB (CURDATE(), INTERVAL 20 Year)) AND (DATE_SUB (CURDATE(), INTERVAL 30 Year))

 

It returns a MySql error while I run the query using php mysql_query().. But when I run it in phpmyadmin, there is no error.. Please help me to fix this.

 

Thank You

[Ken2k7]

Is the column name brith_date or birth_date? And what's the error you're getting?

[starphp]

 

I have corrected the field name to birth_date .. But still getting the errors on php:

 

New Query is:

 

SELECT * FROM user_details WHERE birth_date BETWEEN (DATE_SUB (CURDATE(), INTERVAL 20 Year)) AND (DATE_SUB (CURDATE(), INTERVAL 30 Year))

 

 

Error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AND (DATE_SUB (CURDATE(), INTERVAL 31 Year))' at line 18

 

[PFMaBiSmAd]

The BETWEEN syntax expects the first value to be the minimum/smallest. You should put the 30 into the first term and the 20 into the second term.

[starphp]

Thank you for your suggestion.

 

I have added single quotes and now there is no error while it running in php. Updated query is:

 

SELECT * FROM user_details WHERE birth_date BETWEEN '(DATE_SUB (CURDATE(), INTERVAL 20 Year))' AND '(DATE_SUB (CURDATE(), INTERVAL 30 Year))'

 

[starphp]

 

Query has no errors.. But it doesn't return any record even though, there are number of records between the specified age range..

 

Please help me to fix it.. I am trying to find the records within a age range: 20 to 30

 

SELECT * FROM user_details WHERE birth_date BETWEEN '(DATE_SUB (CURDATE(), INTERVAL 30 Year))' AND '(DATE_SUB (CURDATE(), INTERVAL 20 Year))'

 

[sqlnoob]

are you sure you can do a function within a command? (sorry noob myself)

 

also you failed to switch 20 with 30 in the last post

 

and you are manipulating/calculating the same value/variable twice, in php at least that is not possible unless you make a reference, I don't know wether that is acceptable in sql (I'd also have to check the manual for that)

[PFMaBiSmAd]

DATE_SUB returns a DATE string, the single-quotes and the extra () are unnecessary -

 

SELECT * FROM user_details WHERE birth_date BETWEEN DATE_SUB (CURDATE(), INTERVAL 30 Year) AND DATE_SUB (CURDATE(), INTERVAL 20 Year)

 

If that does not work, I would suggest that your birth_date column is not what you think it is. Post your table definition and an example of what is in birth_date.