mysql - Dates not importing correctly

Question

I know similar questions have been posted before, but when I try to follow similar approaches as per the suggestions in the comments, it simply does not help. My query is the following:

LOAD DATA INFILE 'File.txt'
IGNORE
INTO TABLE table_name
FIELDS TERMINATED BY '^~'
LINES TERMINATED BY '
'
IGNORE 1 ROWS
(RUN_DATE, PROC_DT, STL_DT, TRD_DT)
SET RUN_DATE = STR_TO_DATE(RUN_DATE, '%d-%b-%y'); 

The records in the file look something like this:

RUN_DATE^~PROC_DT^~STL_DT^~TRD_DT
21-DEC-20^~23-DEC-20^~23-DEC-20^~21-DEC-20

The dates that get loaded are all populated as '0000-00-00 00:00:00' which I know are the default values when there is a datatype error and IGNORE is used. From what I found online, the issue has to do with the in-file date not being in yyyy-mm-dd format which is the default for mySQL, but the '%d-%b-%y' in the STR_TO_DATE function should help alleviate this issue since

%d: Day of the month as a numeric value (01 to 31) -

%b: Abbreviated month name (Jan to Dec) -

%y: Year as a numeric, 2-digit value

Why is this not helping? I also tried making the months lower case using LOWER() thinking maybe the abbreviated months needed to be all lower case, but this produces the same result. What am I missing here?

question from:https://stackoverflow.com/questions/65660745/dates-not-importing-correctly

Answer 1

To read from the file but store a modified value, you need to use variables:

LOAD DATA INFILE 'File.txt'
IGNORE
INTO TABLE table_name
FIELDS TERMINATED BY '^~'
LINES TERMINATED BY '
'
IGNORE 1 ROWS
(@RUN_DATE, @PROC_DT, @STL_DT, @TRD_DT)
SET RUN_DATE = STR_TO_DATE(@RUN_DATE, '%d-%b-%y'),
    PROC_DT = STR_TO_DATE(@PROC_DT, '%d-%b-%y'),
    STL_DT = STR_TO_DATE(@STL_DT, '%d-%b-%y'),
    TRD_DT = STR_TO_DATE(@TRD_DT, '%d-%b-%y');