I have a table that contains a field named date of type timestamp. I would retrieve only rows that of a specific date.
My PHP script receive date in format dd-MM-YYYY
This is how I parse date for query:
$converted_date = date("Y-m-d", strtotime($date));
This is my WHERE clause:
WHERE DATE(date) = '" . $converted_date . "' ORDER BY date DESC"
I always obtain an empty result.
As mentioned here
NEVER EVER use a selector like DATE(datecolumns) = '2012-12-24' - it is a performance killer:
It will calculate DATE() for all rows, including those, that don't match
It is much faster to use
SELECT * FROM tablename
WHERE columname BETWEEN '2012-12-25 00:00:00' AND '2012-12-25 23:59:59'
You would need to adjust your where clause to return all of the records between midnight and midnight of that day.
$converted_date = date("Y-m-d", strtotime($date));
//$converted_date2 would equal that day up to 23:59:59
WHERE date BETWEEN '" . $converted_date . "' AND '.$converted_date2.' ORDER BY date DESC"
Although you could also use
WHERE date >= CURDATE()
