How to select records between 2 dates stored as varchar using prepared statement?

[angel1987]

I have been searching on internet for quite a while now for the query, tried many but none work, so i am resorting to asking myself.

 

I have a table column with type varchar that stores date in format mm-dd-yyyy. I need to fetch all records from this table with the dates within next 4 days (between today and next 4 days).

 

Also, i am using mysqli prepared statements to fetch this record.

 

Below is the query which i tried, it works but not accurately.

if ($stmt = $mysqli->prepare("SELECT names FROM members WHERE birthdate - INTERVAL 4 DAY > NOW() AND gender = ?")) {

The above query displays records with dates after 4 days from today. I tried changing combinations for + - > and < like i had no idea what i was doing but each combination gave funny results.

 

Has anyone tried this or knows how to do it? Please help me with this query, thanks in advance for your help.

[Barand]

Date arithmetic (and all other MySQL datetime functions, comparisons and sorting) will not work with dates in mm-dd-yyyy format.

 

Don't use varchar for dates, use DATE type fields (format YYYY-MM-DD).

 

Also, if working with just dates, use CURDATE() and not NOW() as the latter contains time element. Unless it is exactly midnight 2013-09-08 doesn't = NOW()

 

You can convert varchar dates to DATE using STR_TO_DATE() but then you need it in every date calculation.

 

 

edit:

If you date is actual birthdate, including year of birth, then it won't be in the range of the next 4 days

[cataiin]

try

WHERE birthdate BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 4 DAY)

[angel1987]

@Barand: Yes, actually its not a birthday, i just replaced the actual column name randomly to hide it.   Thanks for all the information.

@Cataiin: This query worked for me, thanks