I have a table called "actions" with a DATETIME
column called "occurred". I'm trying to return the records for today by doing the following
SELECT * FROM `actions` WHERE `occurred` = DATE(NOW());
But I get an empty result set. If I take the WHERE
clause out, I can see all 295 rows in the table and there's at least 30 rows from today. Later I will be writing another query to return all records between today's date and X amount of days in the past but before I can get there I need to know why this query is returning an empty result set.
Thanks in advance.
Simply use the CURDATE() function to get the current date. The date can be displayed in two different formats: ' YYYY-MM-DD ' if it is used in a string context or YYYYMMDD if it is used in a numeric context. There are two other functions that can be used instead of CURDATE() : CURRENT_DATE and CURRENT_DATE() .
MySQL NOW() Function The NOW() function returns the current date and time. Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS. uuuuuu (numeric).
In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column.
SELECT * FROM actions WHERE DATE(ocurred) = CURDATE();
DATE(ocurred)
ignores the time part.
Here's the SQL Fiddle to play with the data: http://www.sqlfiddle.com/#!2/81708/2
If there in no future date in occurred
, you could just use below:
SELECT * FROM `actions` WHERE `occurred` > DATE_SUB(CURDATE(), INTERVAL 1 DAY);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With