Truncate the time part of the datetime field when doing the compare AND DATE_FORMAT(customer.created_dt, '%Y-%m-%d 00:00:00') BETWEEN '2008-12-30' AND '2008-12-30'
Should work. Probably not the most efficient. The other options would be to use take end date + 1 day, minue 1 second. That's even a bigger hack but it would probably be more efficient than converting all of the dates on the fly if you have a large number of records to process. ________________________________ From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wed 12/31/2008 10:13 AM To: MySQL General List Subject: Compare DATETIME to DATE Hi, I don't quite understand (or even know) what the "proper" way to compare a DATETIME column to a given DATE value is. I've used various methods but I'd like to know if there's a better way to compare these values. Right now I have a query with this in the WHERE clause (customer.created_dt is a DATETIME): AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12-30' This was working (MySQL on Win32) before I moved the database to MySQL on RHEL 64-bit (5.0.45-log). Should that work? I've also done this: AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30 23:59:59' That works on both servers, but I really don't want to have to put the time in there (unless that's the way you are supposed to do this). I've though about using DATE_FORMAT... not sure about that either. ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net