DATE_SUB结果为null

如果DATE_SUB()函数的第一个参数格式错误，日期无效或为NULL，则DATE_SUB()函数将返回NULL。这意味着在使用DATE_SUB()函数时，如果传递的日期格式不正确，或者日期是无效的或为NULL，那么函数将无法计算出正确的结果，而是返回NULL。

举个例子，如果我们使用DATE_SUB()函数计算一个无效的日期：

SELECT DATE_SUB('2017-02-29', INTERVAL -1 DAY) result;

结果将返回NULL。

同样地，如果我们将一个无效的日期或者NULL作为传递给DATE_SUB()函数的第一个参数：

SELECT DATE_SUB('03/07/2017', INTERVAL 1 DAY) result;

或者

SELECT DATE_SUB(NULL, INTERVAL 1 DAY) result;

结果也将返回NULL。

所以，当使用DATE_SUB()函数时，一定要确保传递的日期格式正确且有效，否则函数将返回NULL。

相关问题

SELECT DATE_FORMAT( dmtr.`day`,'%Y-%m-%d') as oneday, dmtr.column_num, dmtr.table_num FROM dap_metadata_table_record AS dmtr GROUP BY oneday HAVING oneday >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) UNION ALL SELECT d.`day`, 0, 0 FROM ( SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS day UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) ) AS d LEFT JOIN dap_metadata_table_record AS dmtr1 ON d.`day` = dmtr1.`day` WHERE dmtr1.`day` IS NULL ORDER BY oneday DESC; 修改sql只查询七条

你可以将原来的查询语句中的 `HAVING` 子句改为 `WHERE` 子句，并添加一个条件限制只查询最近七天的记录，修改后的 SQL 查询语句如下：

SELECT DATE_FORMAT(dmtr.`day`, '%Y-%m-%d') AS oneday, dmtr.column_num, dmtr.table_num 
FROM dap_metadata_table_record AS dmtr 
WHERE dmtr.`day` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY oneday
UNION ALL
SELECT d.`day`, 0, 0
FROM (
    SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS day
    UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY)
    UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY)
    UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY)
    UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY)
    UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)
) AS d
LEFT JOIN dap_metadata_table_record AS dmtr1 ON d.`day` = dmtr1.`day`
WHERE dmtr1.`day` IS NULL
ORDER BY oneday DESC
LIMIT 7;

以上 SQL 查询语句中，`WHERE dmtr.`day` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)` 子句限制只查询最近七天的记录，`LIMIT 7` 子句限制只查询七条记录。

优化以下这个试图，把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));