Inserting dates

[eMonk]

I'm trying to perform the following query but it's fetching 0 results.

 

$query = "SELECT expiry_date FROM pets WHERE expiry_date BETWEEN CURDATE() - INTERVAL 5 DAY AND CURDATE() AND status = 'Active' AND reminder_sent = 0";
$result = $db->query($query);

$num_results = $result->num_rows;

if ($num_results == 0)
{
echo "No results found.";
exit;
}

 

I have a expiry_date column with the data "2012-02-13" so it should be displayed in the results. The column is varchar(10) and I'm entering in the data manually YYYY-MM-DD format. What am I doing wrong?

[smerny]

should be using datetime instead of varchar

 

or try using str_to_date

 

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

[PFMaBiSmAd]

Your test date of "2012-02-13" is in the future. Your query is trying to match dates BETWEEN 5 days ago AND the current date.

[eMonk]

Your test date of "2012-02-13" is in the future. Your query is trying to match dates BETWEEN 5 days ago AND the current date.

 

How would I get matches between 5 days after the current date?

 

i tried

 

 BETWEEN CURDATE() + INTERVAL 5 DAY AND CURDATE()

 

but still no results.

[PFMaBiSmAd]

The between comparison is: BETWEEN min AND max

 

BETWEEN CURDATE() AND CURDATE() + INTERVAL 5 DAY

[eMonk]

Ah I see... Thanks PFMaBiSmAd it's working now!