You are here: Re: Text field as date (MySQL) « PHP Programming Language « IT news, forums, messages
Re: Text field as date (MySQL)

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация