mysqlsqldynamicquerydynamic-queries

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'

Solution

  • 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;