I cannot change the data type of the 'Date' field in my table (bus_delay) from text into date.

When I run the following:

UPDATE bus_delay
SET Date = str_to_date(Date, "%d-%m-%y");

I receive the action response: Error Code: 1411. Incorrect datetime value: '1-Jan-22' for function str_to_date

I believe I am using STR_TO_DATE() correctly, please correct me if I am wrong.

Note: When I execute:

DESCRIBE ttc_sql_project.bus_delay;

The field 'Date' returns a type of 'text'.

CodePudding user response：

The format string is wrong. This:

"%d-%m-%y"

Is looking for a date in this format:

"01-01-22"

But the format you have is:

"1-Jan-22"

Fix the format string:

"%e-%b-%y"