I cannot be too specific without knowing the exact database table structure and data, but it seems that your date
values are being stored and processed as:
dd
[delimiter]
mm
[delimiter]
yyyy
(delimiters may logically be /
or -
)
For instance, if you are storing the date as a VARCHAR
and the string looks like 22/08/17
, 30/08/17
,23/11/17
,30/11/17
then it is easy to see that mysql is sorting your rows by reading the dates as "strings" (from left to right) versus "dates" (y
, m
, then d
logic).
There will be many ways to solve this issue, but the best advice that I can give is to correct the data storaged (because date values should be stored as date type) and adjust the codes that access it. This would mean setting the date
column to DATE
type and feeding it values in the format of yyyy-mm-dd
. Then ordering will be simplified permanently, but you will need to adjust the date values in the SELECT
clause to your desired format.
If you cannot or do not wish to correct the data storage, you can put a bandage on the problem by modifying all of the queries that need to order by date.
If your date column is named date
and your values use /
as delimiters, then you can use this ORDER BY
clause to sort by year, then month, then day from newest to oldest:
ORDER BY STR_TO_DATE(`date`, '%d/%m/%Y') DESC
Here is an sqlfiddle to demonstrate what is happening with the STR_TO_DATE()
call.