View Full Version : SQL SELECT ing for WHERE only DATE meets in DATETIME field

11-09-2011, 08:39 AM
Hi guys,

Have a table with a field storing values in DATETIME.
Say the below records.
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. :D

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

Your help is greatly appreciated.

11-09-2011, 06:04 PM

11-09-2011, 06:06 PM
It won't work because you haven't asked it to.
Is date your column name? You should name it something that isn't a reserved word (or possible reserved word), but you need to extract the DATE out of this DATETIME field and compare that.

DATE(yourdateandtimecolumn) ='2011-11-08'

Old Pedant
11-09-2011, 09:08 PM
But you *can* do it without renaming the field if you put the field name between back ticks (` which shares keyboard key with ~):

SELECT list, of, fields FROM table WHERE DATE(`date`) = '2011-11-08'

11-09-2011, 10:10 PM
Question: When is a reserved word not a reserved word?
Answer: When mysql says so!

Old Pedant
11-10-2011, 01:33 AM
Question: When is a reserved word not a reserved word?
Answer: When mysql says so!

No disagreement. But MySQL (and, indeed, all DBs) also say you can use a reserved word as a table or field name if you "escape" it. The escape mechanism differs from DB to DB, but it's always there.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum