On 1/11/07, Dan Nelson <[EMAIL PROTECTED]> wrote:
In the last episode (Jan 11), Jake Peavy said: > On 1/11/07, Gilles MISSONNIER <[EMAIL PROTECTED]> wrote: > >How could I load in the database, data from a text file containaing > >date in a NOT MySQL standard date format [precisely char(10)], so > >that I get the date into a MySQL standard date format in a column of > >type "date" ? > > > >an other way to ask my question : how do I transform a text > >"15/10/1999" into a date "1999-10-15" when I load data from a text > >file into a MySQL database ? > > > >I know that I could use a script to rewrite the text "15/10/1999" as > >text "1999-10-15", and then load the file into Mysql (mysql will > >accept the "1999-10-15" as a date format). I think that I might take > >advantage of STR_TO_DATE, but I dont' know how. > > For LOAD DATA INFILE, there's no way to "intercept" the data between your > file and the insertion in order to massage/modify the data. According to http://dev.mysql.com/doc/refman/5.0/en/load-data.html , you can fiddle with columns during a load, by using the SET clause. You can load the date into a user variable, then SET realdatefield=STR_TO_DATE(@uservar, '%d/%m/%Y');
Ah, a relatively new addition (MySQL >= v5.0.3). Thanks for pointing this out - I've run across dilemmas similar to the OPs in the past. -- -jp Chuck Norris' dog is trained to pick up his own poop because Chuck Norris will not take shit from anyone.
