MySQL date format DD/MM/YYYY select query?
MysqlMysql Problem Overview
I'm a bit confused on how to order by date formats.
For the format YYYY-MM-DD you would do this: ...ORDER BY date DESC...
How would you order by DD/MM/YYYY?
This isn't working:
SELECT * FROM $table ORDER BY DATE_FORMAT(Date, '%Y%m%d') DESC LIMIT 14
Mysql Solutions
Solution 1 - Mysql
Guessing you probably just want to format the output date? then this is what you are after
SELECT *, DATE_FORMAT(date,'%d/%m/%Y') AS niceDate
FROM table
ORDER BY date DESC
LIMIT 0,14
Or do you actually want to sort by Day before Month before Year?
Solution 2 - Mysql
You can use STR_TO_DATE() to convert your strings to MySQL date values and ORDER BY the result:
ORDER BY STR_TO_DATE(datestring, '%d/%m/%Y')
However, you would be wise to convert the column to the DATE data type instead of using strings.
Solution 3 - Mysql
SELECT DATE_FORMAT(somedate, "%d/%m/%Y") AS formatted_date
..........
ORDER BY formatted_date DESC
Solution 4 - Mysql
Use:
SELECT DATE_FORMAT(NAME_COLUMN, "%d/%l/%Y") AS 'NAME'
SELECT DATE_FORMAT(NAME_COLUMN, "%d/%l/%Y %H:%i:%s") AS 'NAME'
Reference: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
Solution 5 - Mysql
SELECT DATE_FORMAT(COLUMN_NAME, "%d/%m/%Y %h:%i %p");
OR
SELECT DATE_FORMAT("2019-05-10 19:30:10", "%d/%m/%Y %h:%i %p");
> OUTPUT is 10/05/2019 07:30 PM
Solution 6 - Mysql
for my case this worked
str_to_date(date, '%e/%m/%Y' )
Solution 7 - Mysql
If the hour is important, I used str_to_date(date, '%d/%m/%Y %T' ), the %T shows the hour in the format hh:mm:ss.
Solution 8 - Mysql
ORDER BY a date type does not depend on the date format, the date format is only for showing, in the database, they are same data.