[MySQL] Order by custom Date

Prosaca · 06-30-2011, 07:15 AM

Hello. I'm actually tryinng to order all by DATE on MySQL but seems not to be possible.

The date order is like this for example: Wednesday, June 29, 2011, 18:31

I tried with this, but doesn't work:
SELECT * FROM members ORDER BY date ASC
Sometimes it works with DESC.

And also with this, and same!
SELECT * STR_TO_DATE( date, '%D, %M %d, %Y, %H:%M' ) AS date_for_sort FROM `members` ORDER BY date_for_sort;

I have this problem and I need to fix it asap for the business.

Thank you for helping,
keep the good work!

opnsrc · 06-30-2011, 09:14 AM

It would depend on what type the date field was originally created as when the table was created. I have a hunch that it's simply a character (char) based field with XX amount of space. That would render your second approach where you're attempting to STR_TO_DATE useless as a char field won't know those values. Do a 'SHOW columns from members;' and you'll see the assigned value.

fbianconi · 06-30-2011, 09:23 AM

I think the consult should look like this

SELECT * STR_TO_DATE( date, '%W, %M %d, %Y, %H:%i' ) AS date_for_sort FROM `members` ORDER BY date_for_sort;

a reference to formats of dates can be found here