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

Querying MySQL Dates in DD/MM/YYYY Format

In MySQL, ordering dates is typically based on the YYYY-MM-DD format, as described in the given example. However, certain scenarios may require sorting dates in a different format, such as DD/MM/YYYY. This article explains how to modify your query to achieve this alternative date ordering.

Incorrect Approach:

The code provided attempts to use DATE_FORMAT to convert the date to a numeric string:

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

However, this approach is incorrect as it simply sorts the numeric strings, not the actual dates.

Correct Approach:

To properly sort dates in DD/MM/YYYY format, you have two options:

1. Format Output:

If you only want the output to be formatted in DD/MM/YYYY without altering the actual sort order, use the following query:

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

In this case, the date values remain in their original format, but a new column, niceDate, is added that displays the dates in DD/MM/YYYY format.

2. Sort by Date Components:

If you actually need to sort the dates by day, then by month, and then by year, use a more complex query:

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

This query extracts the individual date components (day, month, and year) and sorts them in descending order based on the specified rules.

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