[SOLVED] MySQL INSERT INTO SELECT not matching records are triggered by function str_to

MySQL INSERT INTO SELECT not matching records are triggered by function str_to_date

I'm having a weird behavior of MySQL INSERT SELECT where I need to convert the dt_int from TABLE2 to the date time dt in TABLE1.

The table structure is

TABLE1
PK INT(11) -- auto increment
dt datetime

TABLE2
PK INT(11)  -- auto increment
dt_int INT(11)

I have as insert select query like this

INSERT INTO TABLE1(dt)
(
  SELECT str_to_date(dt_int, '%Y%m%d')
  FROM TABLE2 
  WHERE str_to_date(dt_int, '%Y%m%d') IS NOT NULL
)

It works fine if all the dates in the table are valid. However if the table consists of data similar like this

TABLE2
PK   |   dt_int
1        20201209
2        20202020

it would hit Error Code 1411: Incorrect datetime value '20202020' for function str_to_date.

The internal select statements returns only the valid dates, but the insert statements still try to converts the date for those that are filtered. Why is this happening? Is there anything I can do?

[Edited] The MySQL version is 5.7 and engine is InnoDB. Currently hosted in Windows environment.

Solution

INSERT INTO table1
SELECT PK, CONCAT(dt_int DIV 10000, '-1-1') 
           + INTERVAL dt_int MOD 10000 DIV 100 - 1 MONTH 
           + INTERVAL dt_int MOD 100 - 1 DAY
FROM table2
WHERE 0 + DATE_FORMAT(CONCAT(dt_int DIV 10000, '-1-1') 
                      + INTERVAL dt_int MOD 10000 DIV 100 - 1 MONTH  
                      + INTERVAL dt_int MOD 100 - 1 DAY, '%Y%m%d') = dt_int;

fiddle