Just thought I would take this opportunity to wish everyone on the list a happy, prosperous and peaceful 2009.
John Daisley Email: john.dais...@butterflysystems.co.uk Mobile: 07812 451238 MySQL Certified Database Administrator (CMDBA) MySQL Certified Developer (CMDEV) MySQL Certified Associate (CMA) Comptia A+ Certified Professional IT Technician On Wed, 2008-12-31 at 10:23 -0800, Gary W. Smith wrote: > 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 > > > > > ______________________________________________ > This email has been scanned by Netintelligence > http://www.netintelligence.com/email
