You still did not send your table definitions.
Jeremy Brown [InfoSend] wrote:
Michael,
Thanks for the response. My query was kind of long, so I was just trying to simplify. Should have included it anyways.
Here is my query:
select REFCLIENTS.sample, TRACKING.* from REFCLIENTS, TRACKING where TRACKING.ccode LIKE 'ABC' AND REFCLIENTS.code = TRACKING.ccode AND TRACKING.jobtype = 1 AND TRACKING.completed >= '2004-07-21 00:00:00' AND TRACKING.completed <= '2004-07-23 23:59:59' order by TRACKING.jobid desc
The BETWEEN statement is similar:
select REFCLIENTS.sample, TRACKING.* from REFCLIENTS, TRACKING where TRACKING.ccode LIKE 'ABC' AND REFCLIENTS.code = TRACKING.ccode AND TRACKING.jobtype = 1 AND TRACKING.completed BETWEEN '2004-07-21 00:00:00' AND '2004-07-23 23:59:59' order by TRACKING.jobid desc
Oddly enough, when I ran what you suggested:
select * from TRACKING where completed = '2004-07-21'
Empty set! But there are rows in the table with that date.
That is expected if completed is a datetime field. You only supplied the date portion.
try:
select * from TRACKING where completed like '2004-07-21%'
If I run either:
select * from TRACKING where completed > '2004-07-21' - or - select * from TRACKING where completed >= '2004-07-21'
I get the desired results, particularly in the last case where I get dates with 2004-07-21!
I do have a standard INDEX on the "completed" field, would that have something to do with it?
Again, "completed" is a DATETIME field. I have this same table duplicated on 3.23.58 and 4.0.17 with the same results. I'm probably just missing something small that I can't see, but frustrating nonetheless.
Thanks,
Jeremy
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
