How do i change mysql date format from dd mm to yyyy?

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

How can I change date format from DD MM YYYY to Yyyymmdd in MySQL?

How can get date in dd mm yyyy format in MySQL?

Can we change date format in MySQL?

What format is MySQL date?