MySQL date format DD/MM/YYYY select query?

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?

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.

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

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

Related