Sort results

zzz

I have a table that has date recorded in the following format:
04-06-2003

The problem is this table cell is not a date but varchar(15) (don't ask why...)

Anyway, I'd like to be able to sort results based on date records. I'd like to go from the most recent down to the oldest.

What's the best way of getting it to work that way?

zhabala

Hello,
if it is in MySQL database then you can use something like this:

SELECT * FROM table ORDER BY STR_TO_DATE(your_field,'%d-%m-%Y') DESC

Zdenek

zzz

This sounds good but I was not able to locate STR_TO_DATE in the manual and obviously it didn't work. I think this is the right direction, I just need the right function.

czambran

I got this from the mysql online manual.

STR_TO_DATE(str,format)
This is the reverse function of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. The date, time, or datetime values contained in str should be given in the format indicated by format. For the specifiers that can be used in format, see the table in the DATE_FORMAT() function description. All other characters are just taken verbatim, thus not being interpreted. If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. mysql> SELECT STR_TO_DATE('03.10.2003 09.20',
-> '%d.%m.%Y %H.%i');
-> '2003-10-03 09:20:00'
mysql> SELECT STR_TO_DATE('10arp', '%carp');
-> '0000-10-00 00:00:00'
mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00',
-> '%Y-%m-%d %H:%i:%s');
-> NULL

Range checking on the parts of date values is as described in section 12.3.1 The DATETIME, DATE, and TIMESTAMP Types. This means, for example, that a date with a day part larger than the number of days in a month is allowable as long as the day part is in the range from 1 to 31. Also, ``zero'' dates or dates with part values of 0 are allowed. mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
-> '0000-00-00'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'

STR_TO_DATE() is available as of MySQL 4.1.1.