Datetime and SELECT * FROM table WHERE date = TODAY
Solution 1
Example, if you will search other dates beside today:
$todaysDate = date("Y-m-d"); # or any other date
$query_today = $db->prepare("SELECT * FROM table WHERE DATE(date) = :date");
$query_today->bindParam(":date", $todaysDate);
$query_today->execute();
Example, only for current date:
$query_today = $db->prepare("SELECT * FROM table WHERE DATE(date) = CURDATE()");
$query_today->execute();
p.s. but like @ypercube said, if you have index on date field, his example will use that index.
$todaysDate = date("Y-m-d");
$query_today = $db->prepare("SELECT * FROM table WHERE date >= :date AND date < DATE_ADD(:date, INTERVAL 1 DAY)");
$query_today->bindParam(":date", $todaysDate);
$query_today->execute();
or
$query_today = $db->prepare("SELECT * FROM table WHERE date >= CURDATE() AND date < DATE_ADD(CURDATE(), INTERVAL 1 DAY)");
$query_today->execute();
Solution 2
This will work whether the column is of type DATE
, DATETIME
or TIMESTAMP
and will use an index, if there is one available:
WHERE date >= CURRENT_DATE
AND date < CURRENT_DATE + INTERVAL 1 DAY
Solution 3
A simple
SELECT * FROM table WHERE date = DATE(NOW())
should suffice.
Or as Alma pointed out,
SELECT * FROM table WHERE date = CURDATE()
Treps
Updated on May 17, 2020Comments
-
Treps about 4 years
The column (date) in the database says "2013-10-14 14:37:38" for example.
How can I edit
$todaysDate
to display ALL dates today? I know that I have to use % somewhere but I have tried a lot of ways without success. :/My code is below:
$todaysDate = date("Y-m-d"); $query_today = $db->prepare("SELECT * FROM table WHERE date=:date"); $query_today->bindParam(":date", $todaysDate); $query_today->execute();
UPDATE Nothing below is working. I just want to select all dates in the database from a simple
date("Y-m-d")
. If the date is "2013-10-14 14:37:38" in the database I want to select it with just 2013-10-14. Something likedate("Y-m-d%")
.