mysql不在之间,MySQL“之间”条款不包含在内？

最新推荐文章于 2021-11-02 03:43:18 发布

转载最新推荐文章于 2021-11-02 03:43:18 发布 · 89 阅读

文章标签：

#mysql不在之间

在SQL中，BETWEEN子句用于选取介于两个值之间的记录，但默认不包含上限值。文章指出，当日期字段包含时间组件时，可能会导致意外地排除掉上限日期。解决方案是通过将日期字段转换为日期类型，如使用CAST函数，确保查询包含整个日期范围。这有助于避免由于时间部分引起的不期望的排除问题。

If I run a query with a between clause, it seems to exclude the ending value.

For example:

select * from person where dob between '2011-01-01' and '2011-01-31'

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

解决方案

The field dob probably has a time component.

To truncate it out:

select * from person

where CAST(dob AS DATE) between '2011-01-01' and '2011-01-31'