How to Correctly Order Dates in DD/MM/YYYY Format in MySQL?

How to Handle Date Ordering for a Specific Format in MySQL?

When it comes to ordering dates in MySQL, the typical approach is to use the DESC modifier for formats like YYYY-MM-DD. However, confusion arises when dealing with different date formats, such as DD/MM/YYYY.

In the specific case of ordering dates in DD/MM/YYYY format, the provided query is not working as expected. The query attempts to apply the DATE_FORMAT function directly to the DATE_FORMAT(Date, '%Y%m%d') expression, which doesn't align with the desired outcome.

To resolve this issue, you have two primary options:

1. Formatting the Output Date Only: If the goal is solely to display the date in DD/MM/YYYY format without altering the actual date value, you can use the following query:

   SELECT *, DATE_FORMAT(date,'%d/%m/%Y') AS niceDate 
   FROM table 
   ORDER BY date DESC 
   LIMIT 0,14

   In this query, the DATE_FORMAT function is applied to the date column with the '%d/%m/%Y' format during the data retrieval process. The results will display the dates in the desired format while leaving the underlying date values unchanged.

2. Actual Sorting by Day, Month, and Year: If you genuinely want to order the results by the day, then month, and finally year, as suggested by DD/MM/YYYY, you will need to use a custom comparison function. This function will extract the individual components of the date and perform a lexicographical comparison. Here's an example query that implements this approach:

   SELECT * FROM table
   ORDER BY FORMAT_DAYOFMONTH(Date), FORMAT_MONTH(Date), FORMAT_YEAR(Date) DESC
   LIMIT 14

The above is the detailed content of How to Correctly Order Dates in DD/MM/YYYY Format in MySQL?. For more information, please follow other related articles on the PHP Chinese website!