MySQL date format DD/MM/YYYY select query?

mysql
basickarl picture basickarl · May 17, 2012 · Viewed 828.4k times · Source

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

Answer

trapper picture trapper · May 17, 2012

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?

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More