This should work, but will not use any index on date_field: SELECT field FROM table WHERE DATE_FORMAT(date_field, "%Y-%m-%d") = '2003-10-10';
This is better and will allow an index to be used if appropriate: SELECT field FROM table WHERE date_field >= "2003-10-10 00:00:00" AND date_field < DATE_ADD("2003-10-10 00:00:00", INTERVAL 1 DAY); -JF > -----Original Message----- > From: Terence [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 09, 2003 8:21 PM > To: [EMAIL PROTECTED] > Subject: MySQL 4.1 Date Comparing > > > Dear All, > > We recently upgraded from 3.23 to 4.1. > > We used to use queries such as: > > SELECT field FROM table WHERE date_field like '2003-10-10%' > > (The date_field is a "datetime" field) > > Since we found the performance quicker than using the date functions. > > However this no longer works in 4.1, and the only way I can > find to do the > above is > SELECT field > FROM table > WHERE YEAR(date_field) = YEAR(curdate()) > AND MONTH(date_field) = MONTH(curdate()) > AND DAY(date_field) = DAY(curdate()) > > Is there a better way, since I cant seem to find any > functions to return the > date_field to a date. > > JFYI, I have tried: > > SELECT ... > FROM ... > WHERE DATE(date_field) = curdate() > > according to the manual > DATE() is available as of MySQL 4.1.1. > > SELECT ... > FROM ... > WHERE CURRENT_DATE(date_field) = curdate() > > > > Thanks! > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]