[SOLVED] (MySQL) Insert by subquery contains date conversion in the condition

(MySQL) Insert by subquery contains date conversion in the condition

I have the following query:

SELECT 'Wrong DATE format'
FROM  DATE_TABLE
WHERE CONVERT(STR_TO_DATE( STATUS_DATE, '%Y-%c-%e %H:%i:%s'), CHAR(20)) \<\> STATUS_DATE;

the column "STATUS_DATE" is of "LONGTEXT" datatype, with value '2014-11-30 00:00:00.000'

the query is working fine, and getting the result.

when I use the query to insert into error log table:

INSERT INTO ERROR_LOG (THE_ERROR)
SELECT 'Wrong DATE format'
FROM  DATE_TABLE
WHERE CONVERT(STR_TO_DATE( STATUS_DATE, '%Y-%c-%e %H:%i:%s'), CHAR(20)) \<\> STATUS_DATE;

the column "THE_ERROR" is of "VARCHAR(4000)" datatype.

I am getting error:

Error Code: 1292. Truncated incorrect datetime value: '2014-11-30 00:00:00.000'

could someone help me with this? where did I go wrong?

I am using the CONVERT() function to make sure the result in the where condition remain as string and to avoid any conversion to DATE.

but there is still implicit date conversion, where and why I am not sure, and why this error when using the insert?

Solution

I found the answer in other questions,, will try to share it here.

it is about the warnings in MySQL.

the query returns result but still with warnings, and with warnings you can not insert or update to tables. so I hade to use:

INSERT IGNORE ...

and now it is working fine.

hope this will help others :) thank you