Strange behavior with str_to_date, possible mixing of tables?

Question

I have an issue with this query and cannot figure out where the problem is coming from. I am trying to run: update earnings_history setDate_formatted= STR_TO_DATE(temp_date, '%m/%d/%Y'); to convert my strings to dates, but I get this error.

Error Code: 1292. Incorrect datetime value: '2015-02-29' for column 'Date_formatted' at row 5610

Yet this query: select temp_date from earnings_history wheretemp_dateREGEXP '[-]'; finds no rows.

I use the %Y-%m-%d formatted in an entirely different table, yet they seem to be mixing?

Answer 1

I think you'll find you have a temp_date field set to 02/29/2015 in your earnings_history table.

Your STR_TO_DATE() call is specifying format of a '%m/%d/%Y'. It's finding a date with that format and parsing it to a standard SQL date format of '%Y-%m-%d'. Then, it tries to insert that date into your typed field. That type won't take an invalid date, so when it sees the invalid date of 2015-02-29, it throws the error. You don't have a %Y-%m-%d-formatted date in your table; you have an invalid date in your expected format of %m/%d/%Y.