mysql - Excel dates cannot be converted to desired format

Question

I am trying to import data from a .csv file into MySQL using:

LOAD DATA LOW_PRIORITY LOCAL INFILE 'file_path_here' 
REPLACE INTO TABLE `pitch_data` 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
'
IGNORE 1 LINES
(id,AT_BAT_NUMBER,etc.,...,ZONE)
SET GAME_DATE = STR_TO_DATE(@GAME_DATE, '%m/%d/%Y')
;

but the conversion of GAME_DATE from MM/DD/YYYY (in the .csv file) to the required format, YYYY-MM-DD isn't happening (I'm receiving the "Error Code: 1292. Incorrect date value: '10/30/2019' for column 'GAME_DATE' at row 1" message).

I have tried changing the format of the GAME_DATE column manually in an .xlsx file to save and copy to the .csv file, but even in the .xlsx file I can't change the format. Strangely, when I select the "Format Cells" option, it indicates that the data are already in the format, YYYY-MM-DD, but they appear as MM/DD/YYYY to the eye. I have tried a similar approach with the "Text to Columns" wizard, but no luck.

Any help would be appreciated!

added code from comment:

Certainly. Here is a subset of the .csv:

id AT_BAT_NUMBER GAME_DATE 
1  79            10/30/2019 
2  79            10/30/2019

and the SQL code:

LOAD DATA LOW_PRIORITY LOCAL INFILE 'FILE_PATH_TO_CSV' 
REPLACE INTO 
TABLE pitch_data 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
' 
IGNORE 1 LINES 
(id,AT_BAT_NUMBER,GAME_DATE) 
SET GAME_DATE = STR_TO_DATE(@GAME_DATE, '%m/%d/%Y') 

question from:https://stackoverflow.com/questions/65854633/excel-dates-cannot-be-converted-to-desired-format

Answer 1

you have to set the user defined variable first like so

LOAD DATA LOW_PRIORITY LOCAL INFILE 'FILE_PATH_TO_CSV' 
REPLACE INTO 
TABLE pitch_data 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
' 
IGNORE 1 LINES 
(id,AT_BAT_NUMBER,@GAME_DATE) 
SET GAME_DATE = STR_TO_DATE(@GAME_DATE, '%m/%d/%Y')