mySQL date range problem

Question

Hey all i am in need of a little help with figuring out how to get a range of days for my select query. Here is the code i am trying out:

 select id, idNumber, theDateStart, theDateEnd
 from clients
 WHERE idNumber = '010203'
 AND theDateStart >= '2010-04-09'
 AND theDateEnd <= '2010-04-09';

This is what the data in the table looks like:

 TheDateStart = 2010-04-09
 TheDateEnd   = 2010-04-11

When testing that code above, it does not populate anything. If i take out the TheEndDate, it populates but with some other tables data as well which it should not do (it should only get one record).

I know the problem is within the two date's. I'm not sure how to go about getting a date range for theDateStart and theDateEnd since if someone tries it, say, on 2010-04-10, it still needs to know its within rage of the 2010-04-09 - 2010-04-11. But right now, it does not...

Any help would be great! :o)

David

Answer 1

Try Following

 select id, idNumber, theDateStart, theDateEnd
 from clients
 WHERE idNumber = '010203'
 AND date_format(theDateStart, '%Y-%m-%d')>= '2010-04-09' AND
     date_format(theDateEnd, '%Y-%m-%d')<= '2010-04-09'

Answer 2

Ok. i got it :o) (YAY go me!)

 select id, idNumber, theDateStart, theDateEnd
 from clients
 WHERE idNumber = 'NB010203'
 AND '2010-04-09' BETWEEN theDateStart AND theDateEnd;

David