MySqlImport - Import a date field not in the proper format

Question

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?

Answer 1

I had this issue in the past. What I had to do was to utilize LOAD DATA and set the appropriate expression here -

[SET col_name = expr,...]

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Answer 2

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).