On 1/11/07, Gilles MISSONNIER <[EMAIL PROTECTED]> wrote:
hello, I rewrite my question in a simpler (?) way : 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. You could use STR_TO_DATE to massage your date if you were using an INSERT statement like: insert into table set date = STR_TO_DATE('15/10/1999', '%d/%m/%Y'); I think your best solution is to repair your date field using Perl before LOAD DATA INFILE. That is, of course, if you can't change whatever it is that generates the file to produce MySQL friendly dates. -- -jp Chuck Norris once ate three 72 oz. steaks in one hour. He spent the first 45 minutes having sex with his waitress.