将各种日期格式更改为一种格式

So I had this code which was buggy and it saved dates randomly in "m-d-yyyy" OR "mm-dd-yyyy" OR "yyyy-mm-dd" OR "yyyy-m-d".

How can I, in a query fetch data from this column so that the date is always in "YYYY-MM-DD" format?

I've tried this, but it doesn't seem to work

DATE_FORMAT(c.tx_date, ('%Y-%m-%d')) as tx_date

Any guidance would be appreciated.

You can use STR_TO_DATE() as the inverse of DATE_FORMAT(). See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

Here's an example to convert your various formats into a proper YYYY-MM-DD date value:

SELECT COALESCE(
  STR_TO_DATE(c.tx_date, '%c-%e-%Y'),
  STR_TO_DATE(c.tx_date, '%m-%d-%Y'),
  STR_TO_DATE(c.tx_date, '%Y-%m-%d'),
  STR_TO_DATE(c.tx_date, '%Y-%c-%e')) AS tx_date
...

You should always avoid storing dates as strings. Using the proper DATE data type avoids this problem of inconsistent formats, and it takes less space.

This is one of the many reasons you should always store date and datetime values in appropriate data types. DATE_FORMAT() is used to format a date value into a string, not convert an ambiguously formatted string from one format to another.

You'll need to create a much more complicated query to normalize your data. It will involve inspecting the current value, and then using something like STR_TO_DATE with an appropriate formatting string (determined by the aforementioned inspection results).