I am attempting to convert a text data type field of days of the week (below) into a date data type field.

When I run:

UPDATE toronto_transport
SET Day = str_to_date(Day, "%W");

I receive the following: 'Error Code: 1411. Incorrect datetime value: 'Saturday' for function str_to_date'

I believe I am following the correct conventions as per MySQL 8.0 reference manual section 12.7 . Please correct me if I am wrong, I am clearly missing something here.

Steps to resolve this error as mentioned above, as well as attempting "%w" and "%x".

CodePudding user response：

You could use a case expression :

update toronto_transport tt
set `Day` = case when `Day`='Sunday'     then 0
                 when `Day`='Monday'     then 1
                 when `Day`='Tuesday'    then 2
                 when `Day`='Wednesday'  then 3
                 when `Day`='Thursday'   then 4
                 when `Day`='Friday'     then 5
                 when `Day`='Saturday'   then 6
             end ;

https://dbfiddle.uk/Xs464MAJ

CodePudding user response：

This question is quite interesting.

I tried to use MySQL WorkBench to reproduce the error you experienced, but failed.

The first time I ran the query

UPDATE toronto_transport
SET Day = str_to_date(Day, "%W");

I received another error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

After the Safe Updates was disabled and I reconnected, the query ran successfully.

However, the running results were not expected. Every cell's value became 0000-00-00. Because the function str_to_date takes a string involving a concrete date, like 2019-03-02, returns a value 2019-03-02 00:00:00 in Date and Time Data Type.

In the query, we put a string weekday without concrete date to the function str_to_date. The function will only return a zero date to us.

Let's come back to the question:

If my understanding is correct, you need to make Monday be Monday, Tuesday be Tuesday ... , but in type of Date and Time Data Type.

Unfortunately, no functions can help us to get this.

The answer offered by @ErgestBasha is an option. You need to be careful that the other code in your project see Monday or Sunday as the first day in a week.

To get a solid converting from sting to date type. We do need concrete dates.

Maybe, you have another column or table involving corresponding concrete dates.

We can look at potentials solutions if you can involved the dates into this question.