query data in mysql using a date in a datetime column

Question

I want to query data in mysql. The problem is the column is datetime(dpost) and i will use a date in the where clause.

SELECT * FROM `uid1000_qposts` WHERE `dpost`="2011-12-06"

this query doesn't return any results. Is there any possible way I can query data using date against a datetime column?

thanks

Nicola Cossu · Accepted Answer · 2011-12-06 10:59:16Z

2

SELECT * FROM `uid1000_qposts` WHERE date(`dpost`) = '2011-12-06'

answered Dec 6, 2011 at 10:59

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chance · Accepted Answer · 2011-12-06 10:58:17Z

1

Try this:

SELECT * FROM `uid1000_qposts` WHERE DATE_FORMAT(dpost,'%Y-%m-%d')='2011-12-06'

answered Dec 6, 2011 at 10:58

chance

6,52714 gold badges48 silver badges71 bronze badges

ajreal · Accepted Answer · 2011-12-06 10:58:32Z

1

WHERE dpost between "2011-12-06 00:00:00" and "2011-12-06 23:59:59"

Another possibilities is using

DATE_FORMAT(dpost,'%Y-%m-%d')="2011-12-06"

but is not recommended,
as it won't make use on any index (even there is)

answered Dec 6, 2011 at 10:58

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Ghostman · Accepted Answer · 2011-12-06 11:01:21Z

1

SELECT * FROM `uid1000_qposts` WHERE date(`dpost`) = '2011-12-06'

answered Dec 6, 2011 at 11:01

Ghostman

6,1129 gold badges36 silver badges53 bronze badges