Message-ID: <37f3cbf50803130135m799582a5j33929e750c41d298@xxxxxxxxxxxxxx>
Date: Thu, 13 Mar 2008 14:05:39 +0530
From: "Vaibhav Informatics" <vaibhavinformatics@xxxxxxxxx>
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
