MySQL - Loading data into Date Fields from Input File

While working on one of the Salesforce Data Migration project, I was using MySQL to upload data to do all kinds of processing on the data. However I faced some issues while loading date and datetime fields.

I tried using LOAD DATA INFILE command

LOAD DATA INFILE 'InputFile.csv' INTO TABLE StageTask
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;

The error I got was something like this

SQL Error (1292): Incorrect date value: '7/2/2006' for column 'ACTIVITYDATE' at row 1

To fix this issue, I used certain variables and then used them to actual columns after applying STR_TO_DATE function.



LOAD DATA INFILE 'InputFile.csv' INTO TABLE StageTask
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (ID, RECORDTYPEID, SUBJECT, @varACTIVITYDATE, STATUS,  
    @varCREATEDDATE, CREATEDBYID, @varLASTMODIFIEDDATE, LASTMODIFIEDBYID, 
    LEGACYACTIVITYID__C, ACTIVITY_TYPE__C, AUTOFOLLOWUP__C, @varCOMPLETED_DATE__C, 
    ORIGINALTASK__C, FOLLOWUPCREATED__C, PRODUCT_LINE__C, CreatedDateOnly, 
    LastModifiedDateOnly, @varActivityDateOriginal, @dummy)
  SET ACTIVITYDATE = STR_TO_DATE(@varACTIVITYDATE, "%m/%d/%Y"), 
    CREATEDDATE = STR_TO_DATE(@varCREATEDDATE, "%Y-%m-%dT%H:%i:%s.000Z"),  
    LASTMODIFIEDDATE = STR_TO_DATE(@varLASTMODIFIEDDATE, "%Y-%m-%dT%H:%i:%s.000Z"),  
    COMPLETED_DATE__C = STR_TO_DATE(@varCOMPLETED_DATE__C, "%m/%d/%Y"),  
    ActivityDateOriginal = STR_TO_DATE(@varActivityDateOriginal, "%m/%d/%Y");


I created variables for each of Date field (see @var) that was not in yyyy-MM-dd format, changed the format using STR_TO_DATE function and BINGO!! it worked flawlessly.

For other date time formats refer to
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

For information on loading data from input file refer to
http://dev.mysql.com/doc/refman/5.1/en/load-data.html