Serge, Why didn't you give this information/constraint in the first place? Why now complain that it is not a "very automated" solution? Why narrow your mind to assume that the problem is with MySQL?
"Them" are going to be a problem to many of their 'clients' then, because CCYY-MM-DD is the international informatique standard. Practically all db users are going to have to fight this - assuming that's what 'them' are expecting will happen to their data. So the problem does not seem to lie with MySQL (or name other RDBMSes)! I don't think it can be done within a single SQL LOAD DATA command. You could do as I suggest, but load the data into a temporary table, handle the reformatting there, and then copy those results into the 'permanent' table. You could pre-process the incoming data file to change the date format, before handing it to the LOAD DATA operation. You could use some 'middleware' to do the whole thing, eg a PHP script, to read in the data file as-is, reformat and perform db INSERT operations. Other ideas? =dn > This is the only option? That's not a very automated process and I must import this data once a day. I also will not be able to get them to reformate their data since I'm only one of many many people downloading everyday. Is their no way for load data to do this automaticaly? > > Serge. > > On Tue, 20 Aug 2002 21:31:45 +0100 > "DL Neil" <[EMAIL PROTECTED]> wrote: > > > Hello Serge, > > > > > I am having troubles with Load Data Infile and cannot figure out the > > problem. I have a date field but the format is 'Jan 1 1986' rather than > > mysql's default. So when I import I only get '0000-00-00' rather than the > > date. How can I tell MySQL what the format is? > > > > > > I have read through the manual and couldn't find anything. Spent about an > > hour now looking and now hoping someone here has the answer. > > > > > > Alter the table by replacing the date field with a temporary replacement > > formatted as text (for example). > > Load the data. > > Alter the table to add the date field back into the table. > > Use SQL functions to format the date according to ISO. > > Alter the table to remove the temporary field. > > > > or > > > > Go back to the original source and have the date formatted correctly (ISO > > standard). > > > > Regards, > > =dn > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
