Error Code: 1411. Incorrect datetime value: '2010-10-13' for function str_to_date

Question

I have this column in a dataset:

 order_date
'2009-01-02'
'2009-01-05'
'2009-01-05'
'2009-01-05'
'2009-01-06'
'2009-01-10'
'2009-01-10'
'2009-01-11'

The dates are in string format so I'm trying to convert them to date format in MySQL workbench using:

UPDATE orders SET order_date = STR_TO_DATE(order_date, '%d-%m-%Y');

But this keeps throwing up this error message:

Error Code: 1411. Incorrect datetime value: '2010-10-13' for function str_to_date

I thought I had the format wrong so I amended the code to '%Y-%m-%d' but that doesn't seem to do anything and just returns:

0 row(s) affected Rows matched: 5506 Changed: 0 Warnings: 0

and the changes don't seem to be taking effect, as querying the datatype shows Type as 'text.'

I'm aware this question has been asked here before and I tried using the solutions like this one but it doesn't seem to work. It looks like there may be some issue with the entries where the date is '2010-10-13' but I can't seem to figure out the solution.

Please let me know where I'm going wrong or what I'm missing. Thanks in advance.

Answer 1

1. Your string data contains the values which matches default datetime format. So they can be used as date values in date context expressions without datatype changing - it will be performed implicitly.

2. STR_TO_DATE() function's 2nd parameter must contain the pattern which must be used for parsing. You specify '%d-%m-%Y' - so the data should contain the values in this format, for example, '13-10-2010', for the parsing to be successful. Your data contains '2010-10-13', the pattern tells that the first digits group is day number, the server extracts this group which is 2010 and reports that this is incorrect day number, and, hence, the whole value is "Incorrect datetime value".