日期SQL查询无法按预期工作

I have the following query:

我有以下查询：

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?

它显示了01-04至28-04的结果。这可能是一个奇怪的问题，但我认为它应该显示29-04的结果，对吗？

What's wrong?

怎么了？

3 个解决方案

#1

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'

我想从您尝试查询数据的方式来看，您的日期列实际上是DATETIME或TIMESTAMP列。如果是这样，那么'2011-04-29％'将被投射到'2011-04-29 00:00:00'

I would recommend you use this SQL instead:

我建议你改用这个SQL：

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

#2

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

这里“％”的目的是什么（除了使日期无效）？

If "date" is of type DATETIME, then :

如果“date”的类型为DATETIME，则：

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

#3

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

您不需要前导％，没有小时的日期被解释为给定日期的午夜（或最开始）。

#1