MS SQL dates -> MySQL Dates Automatically?

[beley]

We're using data feeds that were originally meant for MS SQL and the dates in the data feed (tab delimited text files) are formated like:

Oct 21 2007

In MySQL, the date fields are formatted as datetime fields and when we do an import the dates all come through as 0000-00-00 00:00:00.

We're importing using the MySQL "LOAD DATA LOCAL INFILE" command and just dumping the text files right into freshly truncated tables.

Is there a way when loading the files to find and replace the dates maybe? Can MySQL convert the dates?

I have a shell script downloading an archive and uncompressing, then it runs a PHP script that loops through the files runningt he LOAD DATA command.

Any advice would be appreciated... I'm just looking for the easiest (and least server-intensive) way to get the date issue fixed. We've already tried begging the vendor but they're not yet ready to start supporting other date formats (even though their MS SQL db can output a date format friendly to MySQL).

Thanks!

[guelphdad]

when using load data use a variable to indicate the column in the file that you want to change and then use SET to set how you want that column to be and run DATE_FORMAT on it:

LOAD DATA INFILE 'blah\blah\blah'
INTO TABLE tablename
FIELDS TERMINATED BY 'whatever'
LINES TERMINATED BY 'endofline'
(@junkdate, column2, columnthree, col4four),
SET datecolumn = DATE_FORMAT(@junkdate, '%Y-%m-%d')

NOTE: untested

[beley]

Thanks, I didn't know you could do that with the LOAD DATA command.

I wonder if it could be done just as a search and replace int he shell script? The reason I'm hesitant to use the method above is there are about 8 files (8 tables), and they all have different fields (and a LOT of them) so it would likely take a pretty long time to go through and make a LOAD DATA command for each of them. Right now it's just generic and has a PHP variable echo the name of the table and file.