Everyone,
Thanks for the help. I'm not sure why because I swear on everything holy that I didn't change anything, but the query now works as desired.
Rather than using BETWEEN, I'm just using >= and <=. Perhaps I was missing a quote or something in my old query that was causing it to return the wrong thing.
But in the end, I'm using the proper DATETIME formats for the criteria, which I should have been doing from square one:
WHERE completed >= '2004-07-21 00:00:00' AND completed <= '2004-07-23 23:59:59'
Thanks again for the assistance.
Jeremy
On Sep 22, 2004, at 11:29 AM, gerald_clark wrote:
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
