Mysql DATETIME score: get all records whose value is before midnight of the current day (mostly yesterday and before

Question

It is really simple, but I always struggle with it. I need help getting records before midnight:

AND last_checked < date('2013-06-25 00:00:00')) 

This obviously does not work, since its a string evaluation. I do not want to limit it this year and put a code between them. Any help is much appreciated :)

Answer 1

You can also do this in a general way.

 AND last_checked < ( DATE(NOW()) + INTERVAL 0 SECOND ); 

See this:

 mysql> SELECT DATE(NOW()) + INTERVAL 0 SECOND Midnight; +---------------------+ | Midnight | +---------------------+ | 2013-06-25 00:00:00 | +---------------------+ 1 row in set (0.00 sec) mysql> 

Answer 2

You should just do

 AND last_checked < '2013-06-25 00:00:00' 

Using the date() function simply extracts the date part of the argument.

Answer 3

If last_checked has a datetime data type, then your WHERE will look like this:

 WHERE ... AND cast (last_checked as date) = '2013-06-25' 

CAST (datetime as date) reduces the time, so you can easily get all the data between 00h: 00m: 00s and 23h: 59m: 59s.