MySQL BETWEEN和日期

当使用BETWEEN带有日期值的运算符时，为了获得最佳结果，可以使用类型转换将列或表达式的类型显式转换为DATE类型。

以下示例返回在01/01/2003到01/31/2003之间具有所需日期的订单：

SELECT 
   orderNumber,
   requiredDate,
   status
FROM 
   orders
WHERE 
   requireddate BETWEEN 
     CAST('2013-01-01' AS DATE) AND 
     CAST('2013-01-31' AS DATE); 


+-------------+--------------+---------+
| orderNumber | requiredDate | status  |
+-------------+--------------+---------+
|       10100 | 2013-01-13   | Shipped |
|       10101 | 2013-01-18   | Shipped |
|       10102 | 2013-01-18   | Shipped |
+-------------+--------------+---------+

因为所需日期列的数据类型是DATE的，所以我们使用CAST运算符将文字字符串’2013-01-01’和’2013-12-31’转换为DATE值。