Why doesn’t SET and CONVERT work for updating a table in MYSQL

I have the following code, with the column SaleDate in a datetime format, and I am trying to update it in the table to a date datatype instead

UPDATE nashhousing
SET SaleDate = CONVERT(SaleDate, DATE)

and

UPDATE nashhousing
SET SaleDate = CAST(SaleDate AS DATE)

I’ve tried both cast and convert, but neither modifies the table, does anyone know what’s wrong?

Answer

You need to ALTER the table structure for that:

ALTER TABLE nashhousing Modify column SaleDate date

This will try to convert the date strings into real dates. Check the data afterwards if it succeeded. If not, you need to change the strings first to the right format. The default date format that always works is YYYY-MM-DD.

Of course if this is live data you should test that with a backup table first.