CODEWITHSUNDEEP
mysqlsqldatabasedatetime
Ali
Ali

Reputation: 267077

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

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?

Upvotes: 4

Views: 13343

Answers (3)

Guffa
Guffa

Reputation: 700342

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.

Upvotes: 1

Mark Wilkins
Mark Wilkins

Reputation: 41222

Use the DATE scalar:

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

Upvotes: 11

Tony Bogdanov
Tony Bogdanov

Reputation: 7686

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

Upvotes: 2

Related Questions