Posted by bill on 08/03/07 13:25

Toby A Inkster wrote:
> 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,
Pardon my ignorance, which I am trying to ameliorate.

Q1: Why the WHERE tempcol LIKE '%/%/%' clause ?
It would seem that it would work without it.
Q2: Why 3 %'s ?

bill

[Back to original message]