blocks|key|993007|text|字段dob可能具有时间部分。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|993008|要截断它，请执行以下操作：|993009|select+*+from+person+
where+CAST(dob+AS+DATE)+between+'2011-01-01'+and+'2011-01-31'|code-block|syntax|javascript|993010|entityMap^0|2|3|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

The field <code>dob</code> probably has a time component.

To truncate it out:

<pre><code>select * from person 
where CAST(dob AS DATE) between '2011-01-01' and '2011-01-31'
</code></pre>

blocks|key|778615|text|问题是2011-01-31实际上是2011-01-31+00:00:00。这是一天的开始。白天的一切都不包括在内。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|778616|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The problem is that 2011-01-31 really is 2011-01-31 00:00:00. That is the beginning of the day. Everything during the day is not included.

blocks|key|992930|text|select+*+from+person+where+dob+between+'2011-01-01+00:00:00'+and+'2011-01-31+23:59:59'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|992931|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select * from person where dob between '2011-01-01 00:00:00' and '2011-01-31 23:59:59'
</code></pre>

blocks|key|778722|text|这个查询对我来说很有效，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|778723|select+*+from+person+where+dob+between+'2011-01-01'+and+'2011-01-31+23:59:59'|code-block|syntax|javascript|778724|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Hi this query works for me,

<pre><code>select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'
</code></pre>

blocks|key|993023|text|select+*+from+person+where+DATE(dob)+between+'2011-01-01'+and+'2011-01-31'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|993024|令人惊讶的是，这样的转换是MySQL中许多问题的解决方案。|unstyled|993025|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select * from person where DATE(dob) between '2011-01-01' and '2011-01-31'
</code></pre>

Surprisingly such conversions are solutions to many problems in MySQL.

blocks|key|778523|text|将上限日期设置为日期%2B1天，因此在您的示例中，将其设置为2011-02-01。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|778524|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Set the upper date to date + 1 day, so in your case, set it to 2011-02-01.

blocks|key|778744|text|您可以按如下方式运行查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|778745|select+*+from+person+where+dob+between+'2011-01-01'+and+'2011-01-31+23:59:59'|code-block|syntax|javascript|778746|就像其他人指出的，如果你的约会是硬编码的。|778747|另一方面，如果日期在另一个表中，您可以添加一天并减去一秒(如果保存日期时没有保存秒/时间)，如下所示：|778748|select+*+from+person+JOIN+some_table+...+where+dob+between+some_table.initial_date+and+(some_table.final_date+%2B+INTERVAL+1+DAY+-+INTERVAL+1+SECOND)|778749|避免对dob字段进行强制转换(就像在公认的答案中一样)，因为这会导致巨大的性能问题(比如不能在dob字段中使用索引，假设有索引)。如果您编写了像DATE(dob)或CAST(dob+AS+DATE)这样的代码，那么执行计划可能会从using+index+condition更改为using+where，所以要小心！|offset|length|style|CODE|778750|entityMap^0|0|0|0|0|0|3|3|1B|3|20|9|2A|H|37|L|3V|B|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|Z|8|@$O|10|P|11|Q|R]|$O|12|P|13|Q|R]|$O|14|P|15|Q|R]|$O|16|P|17|Q|R]|$O|18|P|19|Q|R]|$O|1A|P|1B|Q|R]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|T|$]]

You can run the query as:

<pre class="lang-sql prettyprint-override"><code>select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'
</code></pre>

like others pointed out, if your dates are hardcoded.

On the other hand, if the date is in another table, you can add a day and subtract a second (if the dates are saved without the second/time), like:

<pre class="lang-sql prettyprint-override"><code>select * from person JOIN some_table ... where dob between some_table.initial_date and (some_table.final_date + INTERVAL 1 DAY - INTERVAL 1 SECOND)
</code></pre>

Avoid doing casts on the <code>dob</code> fiels (like in the accepted answer), because that can cause huge performance problems (like not being able to use an index in the <code>dob</code> field, assuming there is one). The execution plan may change from <code>using index condition</code> to <code>using where</code> if you make something like <code>DATE(dob)</code> or <code>CAST(dob AS DATE)</code>, so be careful!

blocks|key|778788|text|在MySql中，值之间是包含的，因此当你给出try+to+get介于'2011-01-01‘和'2011-01-31’之间时|type|unstyled|depth|inlineStyleRanges|entityRanges|data|778789|它将包括从2011-01-01+00:00:00到2011-01-31+00:00:00，因此在2011-01-31没有实际内容，因为它的时间应该是从2011-01-31+00:00:00+~+2011-01-31+23:59:59开始的|offset|length|style|CODE|778790|对于上限，您可以更改为2011-02-01，然后它会将所有数据上传到2011-01-31+23:59:59|778791|entityMap^0|0|5|J|P|J|23|15|0|B|A|Y|J|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]|$D|P|E|Q|F|G]|$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$]]

In MySql between the values are inclusive therefore when you give try to get between '2011-01-01' and '2011-01-31' 

it will include from <code>2011-01-01 00:00:00</code> upto <code>2011-01-31 00:00:00</code>
therefore nothing actually in 2011-01-31 since its time should go from <code>2011-01-31 00:00:00 ~ 2011-01-31 23:59:59</code> 

For the upper bound you can change to <code>2011-02-01</code> then it will get all data upto <code>2011-01-31 23:59:59</code>

blocks|key|778796|text|select+*+from+person+where+dob+between+'2011-01-01'+and+'2011-01-31'+or+dob+like'+2011-01-31%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|778797|只要添加or+<<column>>+like+"date%25"即可。|unstyled|offset|length|style|CODE|778798|entityMap^0|0|4|Q|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre class="lang-sql prettyprint-override"><code>select * from person where dob between '2011-01-01' and '2011-01-31' or dob like' 2011-01-31%'
</code></pre>
Just add <code>or &lt;&lt;column&gt;&gt; like &quot;date%&quot;</code>.

If I run a query with a <code>between</code> clause, it seems to exclude the ending value. 
For example:

<pre><code>select * from person where dob between '2011-01-01' and '2011-01-31'
</code></pre>

This gets all results with <code>dob</code> from '2011-01-01' till '2011-01-30'; skipping records where <code>dob</code> is '2011-01-31'. Can anyone explain why this query behaves this way, and how I could modify it to include records where <code>dob</code> is '2011-01-31'? (without adding 1 to the ending date because its been selected by the users.)

MySQL "between" clause not inclusive?

MySQL

如果我使用between子句运行查询，它似乎排除了结束值。例如：select * from person where dob between '2011-01-01' and '2011-01-31'这将获得从'2011-01-01‘到’2011-01-30‘的所有dob结果；跳过dob为'2011-01-31’的记录。谁能解释为什么这个查询是这样的，以及我如何修改它以包含dob为'2011-01

问MySQL "between“子句不包含？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL "between“子句不包含？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL "between“子句不包含？
EN