mysql str_to_date returns NULL value

Question

Objective

I'm trying to load a .csv file called persondata into DB alldata TABLE persondata and then run a query on it for firstname, lastname and dateofbirth (dob). The .csv has only 4 records in it.
Date format in csv is MM/DD/YYYY, the output should be YYYY/MM/DD, YYYY-MM-DD or YYYYMMDD.

SQL

LOAD DATA LOCAL INFILE 'C:/Users/john.smith/Desktop/persondata.csv'
INTO TABLE alldata.persondata
FIELDS TERMINATED BY ','
(firstname, lastname, dob, apptdate, icd9, cpt)
SET dob = str_to_date(@dob, '%c/%e/%Y')
;

SELECT firstname, lastname, dob
FROM alldata.persondata  

Problem and Error Message I'm Getting

firstname and lastname return proper values but dob returns null for all 4 records. In the csv file, the first three colums (A, B, C) are firstname, lastname, dob. So same order as in the table persondata.

Error:

4 row(s) affected, 8 warning(s): 1265 Data truncated for column 'dob' at row 1 1411 Incorrect datetime value: '19850708' for function str_to_date 1265

Help pages I consulted:

(using str_to_date in general)
How to convert csv date format to into mysql db
(using 'SET column = str_to_date...')
MySql load data infile STR_TO_DATE returning blank?
(other)
How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE
Cannot transform mm/dd/yyyy in excel to csv yyyymmdd date format using SSIS
MySQL str_to_date produces NULL despite valid formatting

Additional Information:

I experimented with this query and a bunch of variations of it but no luck:

SET dob = date_format(str_to_date(@dob, '%c/%e/%Y'), '%Y/%c/%e')

I'm not seeing a huge amount of consensus on how to write this. Some people specify the output format of %Y/%d/%m and some don't. But isn't that the only date format that mysql supports? This makes me think I shouldn't have to write it. Not sure if this is even related. I've seen a few syntaxes of the entire thing. I've read through all the support pages and I think that I understand the 'SET' command.

*this is my first post on stackoverflow so please let me know if should present anything differently

Answer 1

You're using "dob" where you should be using "@dob" in your column list... line 4 should be:

(firstname, lastname, @dob, apptdate, icd9, cpt)
                      ^^^^

This is because mysql reads the date into a VARIABLE (indicated by the @), and then the later SET command manipulates the variable to match the actual column (which is identified correctly dob without the @).

Also, I think you're using slashes in your string format when the date doesn't appear to have slashes in it. That is, the error says the date is "19850708" (July 8th, 1985). I believe you want:

%Y%m%d

That is, change line 5 to:

SET dob = str_to_date(@dob, '%Y%m%d')

%m and %d are the 2-digit month and day, which you need since you have '07' instead of just '7' for July, for example. After your comments I'm not sure which date format is correct, but certainly the @dob vs dob variable issue is real. You may want to replace every instance of @dob with something like @original_dob just to alleviate future confusion.

See this page: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date