Incorrect datetime value for str_to_date

Question

I'm trying to copy a varchar column which contains dates in the format YYYY-MM-DD to a date column using str_to_date.

My query is:

UPDATE `subscribers` SET `srtdt` = STR_TO_DATE(`StartDate`, '%Y%m%d%');

I am getting error 1411: Incorrect datetime value: '2001-09-16'... This seems to be happening on the first row.

I have searched a lot for this but cannot find why this is happening - all other answers point towards the string being in the wrong format etc, and that doesn't seem to be the case with my problem.

Answer 1

Try it:

UPDATE subscribers SET srtdt = STR_TO_DATE(StartDate, '%Y-%m-%d');

Answer 2

What you need to be aware of is that in STR_TO_DATE() the format definition parameter is not what you expect the output to be - the output is a mysql date - but what you expect the INPUT to be. i.e. in the op's example it'd be wise to look at the output of

select StartDate from subscribers limit 5;

and if the strings are formatted as yyyy-mm-dd, then use "%Y-%m-%d".

But if the output is appearing formatted eg as (Australian standard) '1 jun 2020' then you'll need "%d %M %Y".