[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: Compare DATETIME to DATE
From:       "Gary W. Smith" <gary () primeexalia ! com>
Date:       2008-12-31 18:23:29
Message-ID: 417DD550405AF84F95040DA44520C8E505193C () pxtbenexd02 ! pxt ! primeexalia ! com
[Download RAW message or body]


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:johnny@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
johnny@pixelated.net



[prev in list] [next in list] [prev in thread] [next in thread]