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

Ordering MySQL Dates in DD/MM/YYYY Format

When dealing with dates in MySQL, understanding the correct syntax for ordering them is crucial. While the YYYY-MM-DD format is typically ordered using "ORDER BY date DESC," the DD/MM/YYYY format requires a different approach.

To order dates in DD/MM/YYYY format, you can employ the following steps:

1. Format the Output Date: If you wish to maintain the original date format while simply modifying how it appears on the output, you can use the following query:

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

This will output the dates in the DD/MM/YYYY format, ordered in descending order.

2. Actually Sort by Date Components: Alternatively, if you want to genuinely sort by the individual date components (i.e., day, month, and year), you can use the following query:

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

This query will sort the dates in descending order, prioritizing the year, then the month, and finally the day.

Note: Make sure that the date field in your database table is of the DATETIME or DATE type for these queries to work correctly.

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