我在 MYSQL 中嘗試了以下查詢，但出現了不正確的日期時間值錯誤。如何解決這個錯誤？
create table Test(id datetime, title varchar(100));
insert into Test(id, title) values('2017-01-11', "Hello");
insert into Test(id, title) values('2018-01-11', "Hello");
SET @trimRetaineddate = '2017-01-11';
SET @delete_text = CONCAT('DELETE FROM Test WHERE id = ', CONVERT(@trimRetaineddate,DATE));
PREPARE delete_stmt FROM @delete_text;
EXECUTE delete_stmt;
select * from Test;
我得到的錯誤：不正確的日期時間值：列“id”的“2005”
uj5u.com熱心網友回復：
MySQL 中的日期文字應該用單引號括起來：
SET @trimRetaineddate = '2017-01-11';
SET @delete_text = CONCAT('DELETE FROM Test WHERE id = ''', @trimRetaineddate, '''');
SELECT @delete_text; -- DELETE FROM Test WHERE id = '2017-01-11'
PREPARE delete_stmt FROM @delete_text;
uj5u.com熱心網友回復：
根據你的拼寫 @delete_text 的結果是 DELETE FROM Test WHERE id = 2017-01-11
MySQL資料庫最后執行的SQL是DELETE FROM Test WHERE id = 2005 (2017-1-11=2005)
因為缺少引號
改成這個就可以執行 SET @delete_text = CONCAT('DELETE FROM Test WHERE id = ','''' ,CONVERT(@trimRetaineddate,DATE),'''');
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/413586.html
標籤：
上一篇：獲取數字和逗號的SQL正則運算式