Sort on string as date on MySql

Question

I have CHAR strings stored in the database field in the format mm/dd/yyyy. Such as

2/26/2022
2/19/2022
2/12/2022
2/5/2022
12/31/2021
12/18/2021
11/27/2021

I need to sort them as shown according to the "date" without changing the declaration.

The post at MySQL date format DD/MM/YYYY select query? suggested using ORDER BY STR_TO_DATE(datestring, '%d/%m/%Y')

My MySQL statement looks like this:

SELECT stringdate
FROM mytable
WHERE product = '#myproduct#'
ORDER BY STR_TO_DATE(stringdate, '%m/%d/%y') DESC

However, the result is not sorted properly. Instead of the desired order as shown above, it is showing like this:

12/31/2021
12/18/2021
11/27/2021
2/26/2022
2/19/2022
2/12/2022
2/5/2022

It seems that the year is being ignored. How can I sort this without actually changing the database field declaration?

Thanks in advance.

Answer 1

%y is the two-digit year code. So you are sorting them all as '20'

%Y is the four-digit year code.

See reference for the date format codes here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

I recommend you use the DATE data type instead of CHAR.