MariaDBで「Incorrect DateTime Value '0000-00-00 00:00:00' - Date_Sub() in Having」エラーが発生する原因と解決方法

2024-04-10

MySQLで「Incorrect DateTime Value '0000-00-00 00:00:00' - Date_Sub() in Having」エラーが発生する原因と解決方法

このエラーは、MySQLで DATE_SUB() 関数を HAVING 句で使用した際に、日付型カラムに不正な値 (0000-00-00 00:00:00) が存在する場合に発生します。

原因

DATE_SUB() 関数は、日付型カラムから指定された期間を減算する関数です。しかし、カラムに存在する値が不正な場合、計算結果も不正となり、エラーが発生します。

解決方法

このエラーを解決するには、以下の方法があります。

不正な値を修正する

カラムに存在する不正な値を修正することで、エラーを解決することができます。修正方法は、以下のいずれかになります。

正しい日付値に更新する
NULL に更新する
レコードを削除する

sql_mode を変更する

MySQLの sql_mode 設定に NO_ZERO_DATE または NO_ZERO_IN_DATE が含まれている場合、0000-00-00 などの日付値は不正とみなされます。これらの設定を無効にすることで、エラーを回避することができます。

DATE_SUB() 関数の代わりに CASE 式を使用することで、不正な値を処理することができます。

例

SELECT
  t.column1,
  CASE
    WHEN t.column2 = '0000-00-00 00:00:00' THEN NULL
    ELSE DATE_SUB(t.column2, INTERVAL 1 DAY)
  END AS column2
FROM
  t
HAVING
  column2 IS NOT NULL;

この例では、column2 カラムに 0000-00-00 00:00:00 値が存在する場合、NULL 値を返し、それ以外の場合は DATE_SUB() 関数を使用して 1 日前を取得しています。

補足

このエラーは、MariaDB でも発生します。

上記の解決方法は、状況によって使い分けてください。

-- テーブル作成
CREATE TABLE t (
  id INT,
  date_col DATETIME
);

-- データ挿入
INSERT INTO t (id, date_col) VALUES
  (1, '2024-04-09 00:00:00'),
  (2, '0000-00-00 00:00:00'),
  (3, '2024-04-10 00:00:00');

-- エラーが発生する例
SELECT
  id,
  DATE_SUB(date_col, INTERVAL 1 DAY) AS yesterday
FROM
  t
HAVING
  yesterday > '2024-04-08 00:00:00';

-- 解決方法 1: 不正な値を修正する
UPDATE t
SET date_col = NULL
WHERE date_col = '0000-00-00 00:00:00';

-- 解決方法 2: `sql_mode` を変更する
SET sql_mode = 'NO_ZERO_DATE,NO_ZERO_IN_DATE';

-- 解決方法 3: `DATE_SUB()` 関数の代わりに `CASE` 式を使用する
SELECT
  id,
  CASE
    WHEN date_col = '0000-00-00 00:00:00' THEN NULL
    ELSE DATE_SUB(date_col, INTERVAL 1 DAY)
  END AS yesterday
FROM
  t
HAVING
  yesterday > '2024-04-08 00:00:00';

実行結果

エラーが発生する例:

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for function str_to_date

解決方法 1: 不正な値を修正する

id | yesterday
------- | --------
1  | 2024-04-08 00:00:00
3  | 2024-04-09 00:00:00

id | yesterday
------- | --------
1  | 2024-04-08 00:00:00
2  | 2023-12-31 00:00:00
3  | 2024-04-09 00:00:00

解決方法 3: DATE_SUB() 関数の代わりに CASE 式を使用する

id | yesterday
------- | --------
1  | 2024-04-08 00:00:00
3  | 2024-04-09 00:00:00

その他の解決方法

DATE_FORMAT() 関数を使用して、日付型カラムを文字列に変換してから、DATE_SUB() 関数を使用することができます。

SELECT
  id,
  DATE_SUB(DATE_FORMAT(date_col, '%Y-%m-%d'), INTERVAL 1 DAY) AS yesterday
FROM
  t
HAVING
  yesterday > '2024-04-08 00:00:00';

SELECT
  id,
  DATE_SUB(SUBSTR(date_col, 1, 10), INTERVAL 1 DAY) AS yesterday
FROM
  t
HAVING
  yesterday > '2024-04-08 00:00:00';

ストアドプロシージャを使用して、エラー処理を独自に実装することができます。

DELIMITER //

CREATE PROCEDURE get_yesterday(IN date_col DATETIME, OUT yesterday DATETIME)
BEGIN
  IF date_col IS NULL THEN
    SET yesterday = NULL;
  ELSE
    SET yesterday = DATE_SUB(date_col, INTERVAL 1 DAY);
  END IF;
END //

DELIMITER ;

CALL get_yesterday('2024-04-09 00:00:00', @yesterday);

SELECT @yesterday;