I have a csv file that has a date field in a format like (among other fields):
17DEC2009
When I do a mysqlimport, the other fields are imported properly, but this field remains 0000-00-00 00:00:00
How can I import this date properly? Do I have to run a sed/awk command on the file first to put it into a proper format? If so, what would that be like? Does the fact that the month is spelled out instead of a number matter?
STR_TO_DATE() enables you to convert a string to a proper DATE within the query. It expects the date string, and a format string.
Check the examples in the manual entry to figure out the correct format.
I think it should be along the lines of %d%b%Y (However the %b is supposed to produce Strings like Dec instead of DEC so you will have to try out whether it works).