When a SELECT query is used as the source for INSERT, some conditions that would just be warnings and return NULL are treated as fatal errors. That’s why the SELECT query works by itself, but not when used with INSERT.

Instead of calling str_to_date() to see if it matches the dd/mm/yyyy pattern, use a simple pattern match with LIKE.

You’re also missing the importacion_id column in the SELECT list.

insert into TbPoblacion(fec_emision, fec_vig_de, fec_vig_a, importacion_id)
SELECT 
CASE WHEN il.c3 LIKE '%/%/%' THEN STR_TO_DATE(il.c3, "%d/%m/%Y") ELSE il.c3 END date_emision, 
CASE WHEN il.c4 LIKE '%/%/%' THEN STR_TO_DATE(il.c4, "%d/%m/%Y") ELSE il.c4 END date_vig_from, 
CASE WHEN il.c5 LIKE '%/%/%' THEN STR_TO_DATE(il.c5, "%d/%m/%Y") ELSE il.c5 END date_vig_to,
il.importacion_id
FROM ImportationLayout il
WHERE il.importacion_id=987654321;