List:General Discussion« Previous MessageNext Message »
From:Jake Peavy Date:January 11 2007 8:06pm
Subject:Re: how to take advantage of STR_TO_DATE
View as plain text  
On 1/11/07, Dan Nelson <dnelson@stripped> wrote:
>
> In the last episode (Jan 11), Jake Peavy said:
> > On 1/11/07, Gilles MISSONNIER <gimi@stripped> 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.

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