sql 'str_to_date' function error truncated incorrect date value and doesn't show the right time format

Question

I have a sql script like this:

LOAD DATA LOCAL INFILE 'XXXXXX/sample.csv'
INTO TABLE test_01
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (id, @DATE_01, @DATE_02, VALUE, @DATE_03)
SET `DATE_01` = STR_TO_DATE(@DATE_01, '%d/%m/%Y'), 
`DATE_02` = STR_TO_DATE(@DATE_02, '%d/%m/%Y'),
`DATE_03` = STR_TO_DATE(@DATE_03, '%d/%m/%Y')

The csv file looks like this:

If I check the format the three dates columns, they are 'dd/mm/yyy hh:mm'

The total rows of my file is 2000+, but I can only get 1100+ rows, the others show me error

1107 row(s) affected, 64 warning(s): 1292 Truncated incorrect date value: '01/12/2010 00:00' 1292 Truncated incorrect date value: '03/02/2011 00:00' 1292 ...........
Records: 2365  Deleted: 0  Skipped: 1258  Warnings: 7095

The dates format in the newly created table looks like this: yyyy-mm-dd 00:00:00

Why the time are all converted to '00:00:00', how can I fix this issue? I'm new to SQL, I've been wrestling with this issue for a whole day now, please help....

This is the expected datetime format:

Answer 1

The format specifier that you are giving to STR_TO_DATE() only includes the date part, not the time portion. You would need to expand it, like so:

LOAD DATA LOCAL INFILE 'XXXXXX/sample.csv'
INTO TABLE test_01
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (id, @DATE_01, @DATE_02, VALUE, @DATE_03)
SET 
    `DATE_01` = STR_TO_DATE(@DATE_01, '%d/%m/%Y %H:%i'), -- here
    `DATE_02` = STR_TO_DATE(@DATE_02, '%d/%m/%Y %H:%i'), -- same here
    `DATE_03` = STR_TO_DATE(@DATE_03, '%d/%m/%Y %H:%i')  -- and here