<[EMAIL PROTECTED]> wrote on 01/16/2006 08:22:00 AM: > > Hi, > > I am trying to load the data from a text file into the table. I > have two datetime datatype fileds in my table. > When I loaded the data into the table using the "Load Data INFILE ...." > syntax, In the table my date fileds values are loaded as "0000-00-00 > 00:00:00". The datetime value in the text file is in "mm/dd/yyyy > hh:mm:ss" format and the datetime format in MySQL is in "yyyy/mm/dd > hh:mm:ss" format by default. Due to the above difference in the format > of datetime, I am getting the loaded datetime value in the table as > "0000-00-00 00:00:00". > > > Could any one of you please suggest me for a solution to load the > correct datetime values from the text file into the tables in the above > scenario. > > Thanking you in advance. > > Thanks, > Narasimha >
Use an intermediate table as the destination of your text file input. Create your intermediate table with mostly varchar columns. Especially make your incoming "date" or "datetime" fields varchar. Then you do a bulk insert from that table to your original destination table using the STR_TO_DATE() function to convert your mm/dd/yy-type dates into valid MySQL date values. http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html I have almost never been able to perform a direct import from a text file into a production table. Using an intermediate table is just one of those things you should get used to doing when you import data from text files. It not only allows you to correct for formatting differences (like the problem you report) but also to correct for invalid data (a process also known as "scrubbing"). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
