How to select from a DATETIME column using only date?

Question

I have a DATETIME column in my table that is stored when I create a record. I want to select only records created on a specific date. If I try:

SELECT * FROM myTable WHERE postedOn = '2012-06-06' 

It does not return rows, although there are many rows in the table, and postedOn set as 2012-06-06 21:42:02 , 2012-06-06 07:55:17 , etc.

Any ideas?

Answer 1

Use the DATE scalar:

 SELECT * FROM myTable WHERE date(postedOn) = '2012-06-06' 

Answer 2

 SELECT * FROM myTable WHERE DATE(postedOn) = '2012-06-06' 

DATE () returns the portion of the date of the datetime field.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date

Answer 3

Create a time range by adding a day to the date:

 SELECT * FROM myTable WHERE postedOn >= '2012-06-06' and postedOn < '2012-06-07' 

This is the most efficient way, since the query can use the index in the field.