Date SQL query doesn't work as expected

I have the following q开发者_Python百科uery:

SELECT * FROM incomings WHERE date >= '2011-04-01%' AND date <= '2011-04-29%'

And it shows results from 01-04 to 28-04. This may be a weird question but, it I think it should show results from 29-04 too, right?

What's wrong?

---

Your syntax is odd. That query would normally be written:

SELECT * FROM incomings WHERE date >= '2011-04-01' AND date <= '2011-04-29'

I think from the way that you're trying to query the data that your date column is actually a DATETIME or TIMESTAMP column. If that's the case then '2011-04-29%' will be being cast to '2011-04-29 00:00:00'

I would recommend you use this SQL instead:

SELECT * FROM incomings WHERE date >= '2011-04-01' AND date < '2011-04-30'

---

What is the purpose of the "%" here (besides making the date invalid) ?

If "date" is of type DATETIME, then :

'2011-04-29 00:00:00' is <= to '2011-04-29'
'2011-04-29 00:00:01' is not <= to '2011-04-29'

---

You don't need the leading %, the date without hours is interpreted as midnight (or the very start) of given date.