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.

CodePudding user response：

2/5/2022 is month and day without leading zeros, and four digit year. The format string you have specified is -

• %m - Month, numeric (00..12)
• %d - Day of the month, numeric (00..31)
• %y - Year, numeric (two digits)

SELECT stringdate
FROM mytable
WHERE product = '#myproduct#'
ORDER BY STR_TO_DATE(stringdate, '%c/%e/%Y') DESC

• %c - Month, numeric (0..12)
• %e - Day of the month, numeric (0..31)
• %Y - Year, numeric, four digits

Executing the following query shows the difference in the converted dates -

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

db<>fiddle

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

CodePudding user response：

%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.