I had data with the date column showing 5 numbers like 43390.

The data looks like this before conversion on Excel:

I converted it to date type in excel and import it using local infile to MySQL Workbench.

Data on Excel after conversion: |date| |:---- |2018/10/17| |2019/05/14|

However, I have been experiencing multiple issues on MySQL that would like to have your help on.

Firstly, when I created a table on MySQL as shown below:

CREATE TABLE df
(
dates DATE,
store_num INT,
loyalty_card_num INT,
txin_id INT,
prod_num INT,
product_name VARCHAR(250),
product_qty INT,
total_sales FLOAT
);

Then imported using code below:

LOAD DATA LOCAL INFILE 'file_location/data.csv' 
INTO TABLE df
FIELDS TERMINATED BY ',' 
ENCLOSED BY "'" 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(dates, store_num, loyalty_card_num, txin_id, prod_num, product_name, product_qty, total_sales);

I got 0000-00-00 on my dates column.

If I changed the dates column type to VARCHAR(20) or INT it only shows year like 2018.

I also tried using

(@dates, store_num, loyalty_card_num, txin_id, prod_num, product_name, product_qty, total_sales);
set dates = date_format(@dates, "%Y-%m-%d)

but returned null values.

I just want it to show the proper date format like 2018-01-01 but really struggle to get the result.

Thank you for your help, let me know if I explained anything unclear.

CodePudding user response：

You may invoke STR_TO_DATE here and convert the text strings in the input file to proper dates as part of the load data process:

LOAD DATA LOCAL INFILE 'file_location/data.csv' 
INTO TABLE df
FIELDS TERMINATED BY ',' 
ENCLOSED BY "'" 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@var1, store_num, loyalty_card_num, txin_id, prod_num, product_name, product_qty, total_sales)
SET dates = STR_TO_DATE(@var1, '%Y/%m/%d');