How to get previous day records from mysql table?

Question

I am trying to extract previous records from a table, but I cannot find how to do this for sure. You need your help ..

Table: RECORD_DATA

id creationDate 1 | 2013-05-03 04:03:35 | 2 | 2013-05-03 04:03:35 | 

Now I need to get all the records created in 2013-05-03. Time can be any. Therefore, my request must have a LIKE statement.

I use the query below and it gives me an empty set.

 select creationDate from RECORD_DATA where creationDate LIKE DATE_SUB(STR_TO_DATE('2012-04-05','%d/%m/%Y'),INTERVAL 1 DAY); 

Answer 1

Simple enough when running with SQL, just add a condition

 WHERE creationDate BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() 

There is no need to convert createDate, as this is already a date :). And I believe that this will be the fastest way to test it (which will make a difference if you go to large data sets).

Answer 2

I think this should do it:

 SELECT * FROM RECORD_DATA WHERE `creationDate` >= CURDATE()-1 and `creationDate` < CURDATE(); 

Answer 3

This will use INDEX in creationDate (if any).

 SELECT * FROM TableName WHERE creationDate >= CURDATE() - INTERVAL 1 DAY AND creationDate < CURDATE() 

Answer 4

you can use

 SELECT * FROM `tableName` WHERE DAY(created) = DAY(CURRENT_DATE - INTERVAL 1 DAY)