SQL SELECTing for WHERE only DATE meets in DATETIME field

[elabuwa]

Hi guys,

 

Have a table with a field storing values in DATETIME.

Say the below records.

Date

2011-11-09 08:07:32

2011-11-08 08:41:04

2011-11-01 08:51:53

2011-11-02 08:52:19

2011-11-05 08:52:56

2011-11-04 08:53:09

2011-11-08 08:53:29

2011-11-08 08:53:59

 

How would I write my sql statement if I need to get the records where only the date matches. Time does not need to be matched. As long as the date is matched I'm happy.

 

I tried writing SELECT * FROM TABLE WHERE date='2011-11-08' but no success. Probably overlooking something.

 

Your help is greatly appreciated.

Cheers

Elabuwa

 

[tomfmason]

there are a few ways of going about this but I think using the DATE function would be the easiest.

 

SELECT `field` FROM `table` WHERE DATE(`datefield`) = 'YYYY-MM-DD';

[fenway]

If you have an index on that field, then :

 

SELECT `field` FROM `table` WHERE `datefield`>= 'YYYY-MM-DD';

 

Is better.

[elabuwa]

thanks guys