PDA

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



elabuwa
11-09-2011, 08:39 AM
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. :D

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

Your help is greatly appreciated.
Cheers
Elabuwa

BubikolRamios
11-09-2011, 06:04 PM
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

guelphdad
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.


SELECT
yourcolumn
FROM
yourtable
WHERE
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'

guelphdad
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.