8

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

There really is no way for me to change the field type so please don't say this is a bad way to do this as I already know. I just need to know if it is possible.

Thanks for any help and advice in advance.

Somk
  • 11,869
  • 32
  • 97
  • 143

6 Answers6

25

You can do it by the following way,

SELECT ...
FROM ...
ORDER BY STR_TO_DATE(yourDate,'%d-%m-%Y') DESC
Vinoth Babu
  • 6,724
  • 10
  • 36
  • 55
4

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')
davek
  • 22,499
  • 9
  • 75
  • 95
1

Use 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 while DATE_FORMAT converts Date to String

Raptor
  • 53,206
  • 45
  • 230
  • 366
1

It depends on how you stored date in your date field. In my case, it is separated by "/" so I used -

...
ORDER BY STR_TO_DATE(report_date, '%m/%d/%Y') DESC;

In case, if you're using "-" to separate date, month and year, then you can use -

...
ORDER BY STR_TO_DATE(report_date, '%m-%d-%Y') DESC;

enter image description here

Sachin Vairagi
  • 4,894
  • 4
  • 35
  • 61
0

use this

STR_TO_DATE

   SELECT * FROM table_name ORDER BY  STR_TO_DATE(date_field, '%d-%M-%Y') DESC
PSR
  • 39,804
  • 41
  • 111
  • 151
0
ORDER BY CONCAT(SUBSTR(field, 7, 4), SUBSTR(field, 4, 2), SUBSTR(field, 1, 2)) DESC
Oswald
  • 31,254
  • 3
  • 43
  • 68