Search code examples
mysqlmysqlimport

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 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?

Solution

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