Jump to content

select date between a range


starphp

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/158082-select-date-between-a-range/
Share on other sites

 

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

 

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))'

 

 

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))'

 

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)

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.

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.