MySQL ORDER BY Date field that is not in date format

Question

I have a field containing dates in this format DD / MM / YYYY, and I need to order DESC results in this field, but it is saved as VARCHAR, and I can’t change it. Is there a workaround?

I really have nothing to change the type of the field, so please do not say that this is a bad way to do this, as I already know. I just need to know if this is possible.

Thanks for any help and advice in advance.

Answer 1

You can do it as follows:

SELECT ... FROM ... ORDER BY STR_TO_DATE(yourDate,'%d-%m-%Y') DESC 

Answer 2

Using STR_TO_DATE :

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date

 ... order by str_to_date(myCol, '%d/%m/%Y') 

Answer 3

use this

STR_TO_DATE

  SELECT * FROM table_name ORDER BY STR_TO_DATE(date_field, '%d-%M-%Y') DESC 

Answer 4

 ORDER BY CONCAT(SUBSTR(field, 7, 4), SUBSTR(field, 4, 2), SUBSTR(field, 1, 2)) DESC 

Answer 5

Use the STR_TO_DATE() MySQL function:

 SELECT * FROM your_table ORDER BY STR_TO_DATE(your_date_column, '%d/%M/%Y') DESC; 

sidenote: STR_TO_DATE converts String to Date, and DATE_FORMAT converts Date to String