MySqlImport - Import a date field not in the proper format

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 开发者_C百科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).

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

Here is the approach I took to solve similar problem. My use case was bit complex with so many columns, but making here simple to present the solution. I have Persons table with (Id int autogen, name varchar(100),DOB date), and few million of data(name,DOB) needs to be populated from CSV file.

1. Created additional column in persons table with name like (varchar_DOB varchar(25)).
2. Imported data using mysqlimport utility into columns(name,varchar_DOB).
3. Executed update query that updated DOB column using str_to_date(varchar_DOB,'format') function.
4. Now, I have expected data populated DOB column.

The same logic could be applied in doing even other kind of data formatting like double,time_stamp etc.