Are you sure those are the results to those queries? ¢,m Your second query will return more because it includes values outside of the date range specified in the first query.
In the second result set you have 2009 and 'invalid dates' that would not be picked up by the first query. Regards John Daisley Email: john.dais...@butterflysystems.co.uk Mobile: 07816 815424 MySQL Certified Database Administrator (CMDBA) MySQL Certified Developer (CMDEV) MySQL Certified Associate (CMA) Comptia A+ Certified Professional IT Technician On Mon, 2008-12-15 at 09:12 -0800, Néstor wrote: > I have a char fiel where I am keeping dates formatted as year-month-day > (2006-10-09) > Now I am trying to find all the records between 2 strings (2 dates). The > 2 queries below > should return the same number of records by they do not. > > My query is this: > SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date >= > '2008-01-01' AND proj_adv_date < '2008-12-16') order by proj_type, > proj_adv_date, proj_bid_date, proj_name ASC; > +---------+---------------+ > | proj_id | proj_adv_date | > +---------+---------------+ > | 181 | 2008-11-25 | > | 217 | 2008-10-27 | > | 136 | 2008-12-01 | > | 219 | 2008-12-08 | > | 225 | 2008-12-11 | > +---------+---------------+ > 5 rows in set (0.00 sec) > > I get only 5 records returned but if I do this query: > SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date > > '2008-01-01') order by proj_type, proj_adv_date, proj_bid_date, proj_name > ASC; > +---------+---------------+ > | proj_id | proj_adv_date | > +---------+---------------+ > | 181 | 2008-11-25 | > | 221 | 2008-12-23 | > | 108 | 2009-01-00 | > | 173 | 2009-03-00 | > | 149 | 2009-10-00 | > | 143 | 2009-7-00 | > | 179 | 2010-04-00 | > | 217 | 2008-10-27 | > | 136 | 2008-12-01 | > | 219 | 2008-12-08 | > | 225 | 2008-12-11 | > | 187 | 2009-01-00 | > | 199 | 2009-01-01 | > | 177 | 2009-02-01 | > | 69 | 2009-03-00 | > | 70 | 2009-03-00 | > | 71 | 2009-03-00 | > | 142 | 2009-03-00 | > | 122 | 2009-04-00 | > | 124 | 2009-04-00 | > | 207 | 2009-04-01 | > | 72 | 2009-07-00 | > | 73 | 2009-07-00 | > | 82 | 2009-07-00 | > | 209 | 2009-10-01 | > | 211 | 2009-10-01 | > | 213 | 2010-03-01 | > +---------+---------------+ > 27 rows in set (0.00 sec) > > thanks, > > Rotsen :-) > > > ______________________________________________ > This email has been scanned by Netintelligence > http://www.netintelligence.com/email
