AND CAST(TRANSACTION_DATE AS DATE) BETWEEN '2023-03-03 00:00:00.0' AND '2023-03-03 00:00:00.0' between 查询不到当天的值怎么办

如果您使用的是 MySQL 数据库，可以尝试使用以下语句查询当天的值：

SELECT * FROM your_table WHERE DATE(TRANSACTION_DATE) = '2023-03-03';

这里使用了 MySQL 的 DATE 函数将 TRANSACTION_DATE 转换为日期格式，然后与指定的日期 '2023-03-03' 进行比较。

如果您使用的是其他数据库，可以查阅其文档，了解如何将日期时间类型转换为日期类型，并进行比较。

相关问题

优化以下这个试图，把2023-06-09作为变量传入，SELECT A.office_id AS distributorId, A.customer_code AS customerCode, A.erp_name AS customerName, A.total_amount AS totalAmount, A.trade_date AS tradeDate, A.customer_type AS customerType, "初次" AS is_first_deal FROM t_first_feedback A WHERE A.`status` = '已成交' AND A.trade_date IS NOT NULL AND A.trade_date >= DATE_FORMAT( DATE_SUB( '2023-06-09', INTERVAL 1 MONTH ), '%Y-%m-01' ) AND A.trade_date <= LAST_DAY( DATE_SUB( '2023-06-09', INTERVAL 1 MONTH )) UNION ALL SELECT B.office_id AS distributorId, B.customer_number AS customerCode, B.customer_name AS customerName, B.total_amount AS totalAmount, B.transaction_date AS tradeDate, "" AS customerType, "重复" AS is_first_deal FROM t_repeat_feedback B WHERE B.transaction_date IS NOT NULL AND B.transaction_date >= DATE_FORMAT( DATE_SUB( '2023-06-09', INTERVAL 1 MONTH ), '%Y-%m-01' ) AND B.transaction_date <= LAST_DAY( DATE_SUB( '2023-06-09', INTERVAL 1 MONTH ))

可以将'2023-06-09'作为参数传入SQL语句，避免硬编码，提高代码的可维护性和复用性。具体实现方式如下：

DECLARE @date DATE = '2023-06-09';

SELECT A.office_id AS distributorId,
       A.customer_code AS customerCode,
       A.erp_name AS customerName,
       A.total_amount AS totalAmount,
       A.trade_date AS tradeDate,
       A.customer_type AS customerType,
       "初次" AS is_first_deal
FROM t_first_feedback A
WHERE A.`status` = '已成交'
  AND A.trade_date IS NOT NULL
  AND A.trade_date >= DATE_FORMAT(DATE_SUB(@date, INTERVAL 1 MONTH), '%Y-%m-01')
  AND A.trade_date <= LAST_DAY(DATE_SUB(@date, INTERVAL 1 MONTH))
UNION ALL
SELECT B.office_id AS distributorId,
       B.customer_number AS customerCode,
       B.customer_name AS customerName,
       B.total_amount AS totalAmount,
       B.transaction_date AS tradeDate,
       "" AS customerType,
       "重复" AS is_first_deal
FROM t_repeat_feedback B
WHERE B.transaction_date IS NOT NULL
  AND B.transaction_date >= DATE_FORMAT(DATE_SUB(@date, INTERVAL 1 MONTH), '%Y-%m-01')
  AND B.transaction_date <= LAST_DAY(DATE_SUB(@date, INTERVAL 1 MONTH));

and cast(TRANSACTION_DATE as date) BETWEEN #{transactionDateStart} and #{transactionDateEnd} 将这个oracl mybatis语法改成 大于等于开始时间，小于结束时间

将该语句改为：

and cast(TRANSACTION_DATE as date) >= #{transactionDateStart} and cast(TRANSACTION_DATE as date) < #{transactionDateEnd}

这里使用了大于等于（>=）和小于（<）符号来替代 BETWEEN 子句。注意，这里使用小于（<）而不是小于等于（<=）符号，因为在日期时间查询中，通常情况下我们不需要包括结束时间那一天的数据。