CODEWITHSUNDEEP
mysqlsqldynamicquerydynamic-queries
Harish Duraiarasan
Harish Duraiarasan

Reputation: 3

Incorrect datetime value error - MYSQL dynamic query

I tried the following query in MYSQL and I am getting Incorrect datetime value error. How to solve this error?

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;

The error I got: Incorrect datetime value: '2005' for column 'id'

Upvotes: 0

Views: 885

Answers (2)

zhigang.chai
zhigang.chai

Reputation: 1

according to your spelling The result of @delete_text is DELETE FROM Test WHERE id = 2017-01-11

The final SQL executed by the MySQL database is DELETE FROM Test WHERE id = 2005 (2017-1-11=2005)

because of missing quotes

Change it to this and it can be executed SET @delete_text = CONCAT('DELETE FROM Test WHERE id = ','''' ,CONVERT(@trimRetaineddate,DATE),'''');

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522516

Date literals in MySQL should be surrounded by single quotes:

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;

Upvotes: 0

Related Questions