[SOLVED] Different behavior of str_to_date in SELECT and INSERT statement

Different behavior of str_to_date in SELECT and INSERT statement

I have the same problem as in this question - MySQL Unable to insert WHERE STR_TO_DATE IS NULL.

I want to migrate dates from one db into another. The dates in the original db are stored as varchars and are not always valid - for example sometimes there is a value like "n.b." or some other string, sometimes they are null. Using str_to_date() in a SELECT statement works fine - if the provided string does not match the provided format, it returns null. This is the behavior that I want, but in an INSERT statement. Unfortunately, when trying to do so, I get following error:

SQL Error (1411): Incorrect datetime value: 'n.b.' for function str_to_date

Do you have some advice to avoid this behavior?

I am using MariaDB if this matters.

EDIT 1: This is my INSERT statement:

insert into person
(id, firstname, lastname, date_of_birth, place_of_birth, gender, old_id)
select
vm.person_id, 
 IFNULL(wv.vorname, '') as firstname, 
 IFNULL(wv.NAME, '') as lastname, 
STR_TO_DATE(wv.geburtsdatum, '%e.%c.%Y') as date_of_birth, 
null as place_of_birth, 
case
    when wv.anrede = 'Herr' then 'm'
    when wv.anrede = 'Frau' then 'w'
    else 'x'
end as gender, 
 vm.old_id
from b.helper_table vm
join a.orig_table wv
on vm.old_id = wv.id;

Solution

It worked with regular expression - thanks to @MatBailie.

insert into person
(id, firstname, lastname, date_of_birth, place_of_birth, gender, old_id)
select
vm.person_id, 
IFNULL(wv.vorname, '') as firstname, 
IFNULL(wv.name, '') as lastname, 
case
    when wv.geburtsdatum REGEXP '^[0-9]{1,2}\.[0-9]{1,2}\.[0-9]{4}$' then 
    str_to_date(wv.geburtsdatum, '%e.%c.%Y')
end as date_of_birth, 
null as place_of_birth, 
case 
    when wv.anrede = 'Herr' then 'm'
    when wv.anrede = 'Frau' then 'w'
    else 'x'
end as gender, 
 vm.old_id
from b.helper_table vm
join a.orig_table wv
on vm.old_id = wv.id;