RE: ordering date in varchar

>Message-ID: <[email protected]>
>Date: Thu, 13 Mar 2008 14:05:39 +0530
>From: "Vaibhav Informatics" <[email protected]>
>
>We have a problem. We have given the dates as date.month.year eg 27.12.2007
>The field is named as varchar. We have to arrange ths list of dates in
>ascending order. Can anyone help? Varchar arranges the list by scanning from
>left side. Perhaps scanning from right side will do. How to do it?


OK I'll take this one too then ;-)

You're right that ORDER BY will treat that as a string, so the 
ordering will be broken.
There are 2 options I can think of to handle this without changing 
the table structure :

One is to split up the string, then re-join it and ORDER BY that as a 
calculated column,
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring


SELECT CONCAT(
         SUBSTRING(datefield, 7, 4),
         SUBSTRING(datefield, 4, 2),
         SUBSTRING(datefield, 1, 2)
) AS dateresult FROM yourdatabase
.
.
.
ORDER BY dateresult ASC


The other is to cast it to a DATE type using STR_TO_DATE, then order by that :
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date


SELECT STR_TO_DATE(`datefield`, '%d.%m.%Y') AS dateresult FROM test
.
.
.
ORDER BY dateresult ASC



TBH though, you have a big headache here because none of that is 
going to be amenable to indexing.

If you end up with several thousand rows after the WHERE statement 
(eg you select all rows) then the performance is going to be 
dreadful. MySQL will have to build a temporary table to hold the 
ordering - it'll do it in memory at first, then to disk if the 
resultset is too large (which is *really* bad for performance).

Your best option if you have any control over the table schema at 
all, is to LOCK TABLES to avoid unintended changes, then create a new 
column perhaps using STR_TO_DATE as above to populate a proper MySQL 
DATE type column.

Modify your application (PHP) script to do the strung 
munging/rearranging on input, so that's a job you only have to do once.

Now you should add an index to the DATE column to get reasonable 
performance if this is a highly trafficked table in which you intend 
to order by the date field a lot.


HTH
Cheers - Neil