...

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



elabuwa
11-09-2011, 07: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, 05:04 PM
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

guelphdad
11-09-2011, 05: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, 08: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, 09:10 PM
Question: When is a reserved word not a reserved word?
Answer: When mysql says so!

Old Pedant
11-10-2011, 12: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