Posted by Toby A Inkster on 08/02/07 14:25

Hendri Kurniawan wrote:

> SELECT * FROM table ORDER BY STR_TO_DATE(coldate, '%d/%m/%Y')

If you've got any more than a few hundred rows, this will perform
horribly. Much better to fix the problem at source.

Assuming your table is called "mytable" and your column is called
"mycol"...

BEGIN TRANSACTION;
ALTER TABLE mytable ADD tempcol varchar(50);
UPDATE mytable SET tempcol=mycol;
ALTER TABLE mytable DROP mycol;
ALTER TABLE mytable ADD mycol datetime;
UPDATE mytable
SET mycol=STR_TO_DATE(tempcol, '%d/%m/%Y')
WHERE tempcol LIKE '%/%/%';
ALTER TABLE mytable DROP tempcol;
COMMIT;

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 42 days, 17:58.]

Open Mobile Alliance DTD Oops!
http://tobyinkster.co.uk/blog/2007/08/02/xhtml-mobile-oops/

[Back to original message]