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:
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.Oct 21 2007
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).