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

Question

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.

Answer 1

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.