I want to change the Date field (from table bus_delay) format using UPDATE, SET and str_to_date() so I may later change the field data type from text to date.

Date field as below:

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"