如何检查MySQL中的日期是否在范围内？

Why do I get different results for this similar queries?

为什么我会为这些类似的查询获得不同的结果？

SELECT CASE WHEN CONCAT(2011, '-1-1') BETWEEN '2011-01-01' AND '2011-03-31' THEN 1 ELSE 0 END AS status;
+--------+
| status |
+--------+
|      0 |
+--------+  

SELECT CASE WHEN CONCAT(2011, '-1-1') BETWEEN '2011-1-1' AND '2011-3-31' THEN 1 ELSE 0 END AS status;
+--------+
| status |
+--------+
|      1 |
+--------+

3 个解决方案

#1

Because you are comparing strings, not dates.

因为您要比较字符串，而不是日期。

The easiest way to turn a string into a date is to wrap in DATE().

将字符串转换为日期的最简单方法是在DATE（）中换行。

SELECT CASE WHEN DATE(CONCAT(2011, '-1-1'))
            BETWEEN DATE('2011-1-1') AND DATE('2011-3-31')
            THEN 1 ELSE 0 END AS status;

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

You can also make your query shorter, since MySQL returns boolean as 1/0 natively.

您也可以缩短查询时间，因为MySQL本身将boolean返回为1/0。

SELECT DATE(CONCAT(2011, '-1-1'))
            BETWEEN DATE('2011-1-1') AND DATE('2011-3-31') as status;

#2

You're comparing strings, not dates. Try explicitly casting to the desired datatype:

你在比较字符串，而不是日期。尝试显式转换为所需的数据类型：

SELECT CASE WHEN CAST(CONCAT(2011, '-1-1') AS DATETIME) BETWEEN CAST('2011-01-01' AS DATETIME) AND CAST('2011-03-31' AS DATETIME) THEN 1 ELSE 0 END AS status;

#3

Because it's doing a string comparison. You should use the mysl date function.

因为它正在进行字符串比较。你应该使用mysl date函数。

 SELECT CASE WHEN date(CONCAT(2011, '-1-1')) BETWEEN date('2011-1-1') AND date('2011-3-31') THEN 1 ELSE 0 END AS status;

#1