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.
