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.
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
Jeremy Brown [InfoSend] wrote:Hello,
I currently have a table with a "completed" DATETIME field. I am trying to run a query that will return all rows *inclusive* of the start and end dates. I have tried the following query:
SELECT `name`, `completed` FROM `table` WHERE `completed` BETWEEN '2004-07-21' AND '2004-07-23';
Why are you comparing completed to DATEs, if completed is a DATETIME? Note that '2004-07-23' is '2004-07-23 00:00:00' when interpreted as a DATETIME.
The problem is that this query will only return rows from 2004-07-22, and does not include rows from 2004-07-21 or 2004-07-23, like I need it to. This could be the desired behavior for the BETWEEN operator (I couldn't seem to find any documentation of it in the documents for some reason), so I also tried:
BETWEEN is inclusive of the endpoints. This is documented in the manual <http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html>.
SELECT `name`, `completed` FROM `table` WHERE `completed` >= '2004-07-21' AND `completed` <= '2004-07-23';
This does the same thing, it only returns dates from 2004-07-22 without including the start or end date.
This tells us BETWEEN is not the problem, as the inequalities produce the same result. Something else is going on. Something strange, but something else.
I have tried adding a time (e.g. 2004-07-21 00:00:00 and 2004-07-23 23:59:59), but again, only 2004-07-22 is returned.
If I remove one of the conditions, I get the correct result. e.g.:
SELECT `name`, `completed` FROM `table` WHERE `completed` >= '2004-07-21';
What does
SELECT name, completed FROM `table` WHERE completed = '2004-07-21';
return?
Will return all rows on *and* after 2004-07-21, as desired. But with both operators, it does not work.
I have tried this query on both 3.23.58 and 4.0.17. I searched the net over and could not find a definitive answer to this problem, so apologies if it's been answered before.
My guess is that your real query is more complicated than what you've shown us (your table isn't really named "table", is it?), and there is some interaction which accounts for this. Perhaps if you showed the full query and the table definition someone could point out the problem/solution.
TIA, Jeremy Brown
Michael
--
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]
