How to select from a DATETIME column using only a date?

Question

I have a DATETIME column on my table which stores when a record was created. I want to select only the records created on a particular date. If I try:

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

It returns no rows even though there are many rows in the table with the postedOn set as 2012-06-06 21:42:02, 2012-06-06 07:55:17 , and so forth.

Any ideas?

Solution 1

Use the DATE scalar:

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

Solution 2

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

DATE() returns the date part of a datetime field.

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

Solution 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, as the query can use an index on the field.