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,293 1 gold badge 21 silver badges 38 bronze badges

asked May 17 '12 at 14:23

8 Answers 8

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,513 8 gold badges 56 silver badges 95 bronze badges

answered May 17 '12 at 14:29

4

  • I would like the 14 latest date/records :) The format in the database is "DD/MM/YYYY" though!

    May 17 '12 at 14:33

  • Is the date in the database an actual date type, or is it a string type?

    May 17 '12 at 14:35

  • mysql> DESCRIBE Table; and paste the output

    May 17 '12 at 14:37

  • DATE_FORMAT(date,'%a') will give a day of week abbreviation. I used this for making some boxplots and such in RStudio.

    Feb 23 '19 at 7:36

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 '12 at 14:28

0 

                  SELECT DATE_FORMAT(somedate, "%d/%m/%Y") AS formatted_date .......... ORDER BY formatted_date DESC

answered May 17 '12 at 14:28

2

  • Is it just me, or does the %l not work? It gives 12 for every month and I had to change to %m.

    Feb 25 '15 at 18:13

answered Jan 20 '15 at 19:49 

                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 '19 at 6:47

for my case this worked 

                str_to_date(date, '%e/%m/%Y' )

Ram Sharma

8,458 7 gold badges 40 silver badges 54 bronze badges

answered Oct 6 '14 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,708 10 gold badges 41 silver badges 107 bronze badges

answered Sep 14 '16 at 18:54

1

  • It's not referred to as hour. %T shows the value with time format. By the way, you got my upvote.

    Jun 17 '18 at 14:12

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 '12 at 14:27

1

  • Format can be used to sort (if someone really want, seems strange for me), but such sort cannot be based on index, so [b]is costly[/b], Only sort over good designed columns can be executed with index (fast) and optimised by server

    Sep 14 '16 at 19:07

Not the answer you're looking for? Browse other questions tagged mysql or ask your own question.