PHP: Search MYSQL database between datetime but use the date only?

Question

I have a column in MYSQL that stores the datetime like so:

2017-08-18 08:59:53
2017-08-18 07:59:00

I can search the database and return the results between the datetime column like so:

$from = date("Y-m-d H:i:s", strtotime($_GET['from']));
$to = date("Y-m-d H:i:s", strtotime($_GET['to']));

$sql1="SELECT * FROM `Table` WHERE mydate>='$to' and mydate<='$from'";

This works fine as long as I search like this:

2017-08-18 08:59:53

But what I need to be able to do is to search like this:

2017-08-18

and still get the same result when I search.

I don't want to use the time in my search string/query.

Answer 1

Try mysql date() function like:

$sql1="SELECT * FROM `Table` WHERE date(mydate)>='$to' and date(mydate)<='$from'";

mysql date() function returns only date part from datetime column in default Y-m-d format.

Answer 2

You are very close. If you want to search a DATETIME or other datestamp column for matches to a particular day, do this:

WHERE mydate >= DATE(whatever_first_day)
  AND mydate  < DATE(whatever_last_day) + INTERVAL 1 DAY

This searches for everything on the date mentioned in whatever_first_day, starting with midnight on that date, and everything up to, but not including (<), midnight on the day after whatever_last_day.

This way of searching is sargable: it can use an index on the mydate column.

You could also use

 WHERE DATE(mydate) >= DATE(whatever_first_day)
   AND DATE(mydate) <= DATE(whatever_last_day)

but that is not sargable.