How to Order MySQL Data by DD/MM/YYYY Date Format?

How to Order by Date Formats in MySQL

When working with date formats in MySQL, you may encounter the need to order your data according to a specific format. While the YYYY-MM-DD format is commonly used, you may also have data in the DD/MM/YYYY format. Understanding the syntax for ordering by different date formats is crucial.

To order by the YYYY-MM-DD format, you can use the following syntax:

...ORDER BY date DESC...

However, for the DD/MM/YYYY format, the syntax you provided:

SELECT * FROM $table ORDER BY DATE_FORMAT(Date, '%Y%m%d') DESC LIMIT 14

will not work.

To correctly order by DD/MM/YYYY, you need to adjust the syntax. If your desired intention is to format the output date, you can use the DATE_FORMAT() function:

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

This will display the dates in the DD/MM/YYYY format.

Alternatively, if you indeed want to sort by Day before Month before Year, you can use a more complex query that includes additional sorting criteria:

SELECT * 
FROM table 
ORDER BY DAY(date), MONTH(date), YEAR(date) DESC 
LIMIT 0,14

By following these guidelines, you can effectively order your MySQL data according to the desired DD/MM/YYYY date format.

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