List:General Discussion« Previous MessageNext Message »
From:Jake Peavy Date:January 11 2007 6:53pm
Subject:Re: how to take advantage of STR_TO_DATE
View as plain text  
On 1/11/07, Gilles MISSONNIER <gimi@stripped> 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.

Thread
how to take advantage of STR_TO_DATEGilles MISSONNIER10 Jan
  • Re: how to take advantage of STR_TO_DATEViSolve DB Team11 Jan
    • Re: how to take advantage of STR_TO_DATEGilles MISSONNIER11 Jan
      • Re: how to take advantage of STR_TO_DATEJake Peavy11 Jan
        • Re: how to take advantage of STR_TO_DATEDan Nelson11 Jan
          • Re: how to take advantage of STR_TO_DATEJake Peavy11 Jan
  • Re: how to take advantage of STR_TO_DATEFelix Geerinckx11 Jan