Different behavior of str_to_date in SELECT and INSERT statement

Question

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;

There is no `n.b` in your query. Are you sure that you are sharing the right query ? — Madhur Bhaiya, Nov 22 '18 at 10:57
I conclude that it's foobared. https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=26157cc83c57587beda704ab6f38cc37 - I think you're going to have to write your own validation, and checking the days value is legitimate for each month will be pain in the backside. — MatBailie, Nov 22 '18 at 10:59
@MadhurBhaiya - The `'n.b.'` is in the data... In the table `a.orig_table wv`... — MatBailie, Nov 22 '18 at 11:00
@MadhurBhaiya: There can be different values in the `wv.geburtsdatum` column, for example "n.b.", null, "some other string". — apmyp1990, Nov 22 '18 at 11:00
`STR_TO_DATE` appears to have a lot of issues. I've raised this one too. https://jira.mariadb.org/browse/MDEV-4635?jql=text%20~%20%22STR_TO_DATE%22 — MatBailie, Nov 22 '18 at 11:12
@MatBailie: Thanks for your fiddle - your regex solution works fine. But now there is a value where the year part has 5 digits. — apmyp1990, Nov 22 '18 at 11:17
Change the regular expression to `string REGEXP '^[0-9]{1,2}\.[0-9]{1,2}\.[0-9]{4}$'`. It won't catch `'31-2-2018'` as being invalid though, `STR_TO_DATE` will still error. For real robustness you need to check everything yourself :(. *(Better to do it before it gets in to the DB.)* — MatBailie, Nov 22 '18 at 11:20
@MatBailie: I just found this solution too. But why it won't catch `31-2-2018` - the regex says that the middle part can contain one or two digits? And the regex is not matching this value: `N552624671` — apmyp1990, Nov 22 '18 at 11:26
There is no 31st of February, but the RegExp will allow it any way, and the `STR_TO_DATE` will error. — MatBailie, Nov 22 '18 at 11:36
The RegExp does seem to filter out `N552624671` : https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=2c4d16bf0abb05ad50da780238b634f0 — MatBailie, Nov 22 '18 at 11:43

score 0 · Accepted Answer · answered Nov 22 '18 at 14:00

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;

Different behavior of str_to_date in SELECT and INSERT statement

1 Answers1