MySQL date matching error?

I have a table which contains a list of ID's (int) and the date (datetime) that ID was created.

One ID in particular was created:

2010-12-31 09:45:29

Wh开发者_开发问答en I run the following query, no results are returned:

select * from information where creation = '2010-12-31'

What gives? I assume this is because of the timestamp (it does not match 2010-12-31 00:00:00) but seems like it should work as a 'date' in general.

select * from information where DATE(creation) = '2010-12-31'

You said it yourself, 2010-12-31 = 2010-12-31 00:00:00, which is not equal to 2010-12-31 09:45:29. You can use DATE() to ensure you are comparing apples to apples.

No, it should not. Try running

select * from information where creation LIKE '2010-12-31%';

or

select * from information where DATE(creation) = '2010-12-31'

try this:

select * from information where DATE_FORMAT(creation,'%Y-%m-%d')  = '2010-12-31'