Hello. When sorting by date, sorting takes place by numbers, but the months are displayed in the reverse order - November, October. Here is the query:

SELECT *, DATE_FORMAT(date, '%d %M %Y') AS `date` FROM

table WHERE start = '$ search' AND end = '$ search1' AND date BETWEEN curdate () AND curdate () + interval 2 month ORDER BY date DESC "

What to fix to get everything right - days and months in ascending order?

  • with ASC instead of DESC, months are turned inside out. - Batyabest
  • I understand DATE_FORMAT returns a string? Then you get the sorting not by the date field table.date, but by the string plugin DATE_FORMAT (date, '% d% M% Y') with an alias of `date - Donil
  • How to be? DATE_FORMAT is used to normalize the date for reading. How then to sort? - Batyabest

2 answers 2

DESC - sorting from large to small. ASC - from the smallest to the largest.

  • This is understandable, with ASC, the numbers are sorted normally, but the months are the opposite. It turns out first goes November 2, then October 5. And vice versa. - Batyabest
  • SELECT *, date AS sortdate , DATE_FORMAT (date, '% d% M% Y') AS date FROM table WHERE start = '$ search' AND end = '$ search1' AND sortdate BETWEEN curdate () AND curdate () + interval 2 month ORDER BY sortdate DESC " - Get
  • Does not work, does not display anything at all. Explain why sortdate is introduced? - Batyabest
  • So that sorting was not by date, reduced to a string type, but by date, in the format datetime. Then the sort order will be correct. You have one alias called date, so the real date field had to be given alias sortdate. Check the variables again, it should work. - Get

In this example, I would do this at the end:

ORDER BY UNIX_TIMESTAMP(STR_TO_DATE( date , '%Y-%m-%d')) DESC

I note - %Y-%m-%d in that order in the database are part of the date.

And yes, it will be sort of easier. And still - date like the reserved word in MySql .