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?

you don't have to try, you just should know. so what is your type? and `temp_date` by the way? — Alex, Apr 18 '15 at 23:34

score 1 · Accepted Answer · answered Apr 18 '15 at 23:46

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.

Yea I just discovered somehow the dates got mixed up when I entered the data in. Trying to input the data again now. Thanks! — nicholas.reichel, Apr 18 '15 at 23:49

Strange behavior with str_to_date, possible mixing of tables?

1 Answers1