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
Alex Moore
3,3851 gold badge22 silver badges39 bronze badges
asked May 17, 2012 at 14:23
basickarlbasickarl
33.5k57 gold badges198 silver badges312 bronze badges
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?
Michel Ayres
5,7039 gold badges60 silver badges97 bronze badges
answered May 17, 2012 at 14:29
4
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.
answered May 17, 2012 at 14:28
eggyaleggyal
120k18 gold badges205 silver badges236 bronze badges
0
SELECT DATE_FORMAT[somedate, "%d/%m/%Y"] AS formatted_date
..........
ORDER BY formatted_date DESC
answered May 17, 2012 at 14:28
John CondeJohn Conde
214k98 gold badges447 silver badges489 bronze badges
2
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
answered Oct 11, 2019 at 6:47
AngularJMKAngularJMK
1,08013 silver badges14 bronze badges
for my case this worked
str_to_date[date, '%e/%m/%Y' ]
Ram Sharma
8,5367 gold badges44 silver badges55 bronze badges
answered Oct 6, 2014 at 9:26
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
.
FelixSFD
5,84110 gold badges43 silver badges111 bronze badges
answered Sep 14, 2016 at 18:54
1
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.
answered May 17, 2012 at 14:27
xdazzxdazz
156k36 gold badges240 silver badges268 bronze badges
1
This image will help you.
SELECT DATE_FORMAT["2019-05-10 19:30:10", "%d/%m/%Y %h:%i %p"];
answered Aug 24 at 17:17