当使用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值。