Incorrect Date Value while loading CSV file to Table

Question

I am trying to Populate data into Employee table where B_Date is set to DATE in Mysql. But getting this error -

Row import failed with error: ("Incorrect date value: '05/06/1985' for column 'B_DATE' at row 1", 1292)

So How do I change the format into DD-MM-YYYY for a particular table or column?

I have tried this -

SELECT DATE_FORMAT(B_DATE, '%d-%m-%y') from employees;

still giving same error while loading Data.

https://stackoverflow.com/questions/11641096/error-while-inserting-date-incorrect-date-value this may help you. — Santosh D.
– Santosh D., Commented Feb 6, 2019 at 7:03
How are you loading the csv file? using load data infile or mysqlwrokbench import wizard? — P.Salmon
– P.Salmon, Commented Feb 6, 2019 at 7:08
You could import the dates inside a (temporary) text column. Then use UPDATE command to fill the date column from the text column using str_to_date. — Salman Arshad
– Salman Arshad, Commented Feb 6, 2019 at 7:21
If mysqlworkbench import wizard does not allow you to transform data then you could do as @Salman A suggests, load to a temporary as text then insert from there to your permanent table or abandon the wizard and use load data infile which does allow transformation an example here stackoverflow.com/questions/8163079/…. — P.Salmon
– P.Salmon, Commented Feb 6, 2019 at 7:26

Deepa MG · Accepted Answer · 2019-11-19 07:04:18Z

2

Last suggestion by Mossad works, explained how here:

To insert date of the form string from csv file to mysql table. You can run the below commands in mysql workbench too.

step1: Covert table date column type to varchar() and import the file.

step2: Update all values of date column by using str_to_date function.

UPDATE Table SET date = STR_TO_DATE(date, '%d-%m-%Y')

step3: Reset date column datatype back to DATE.

ALTER TABLE Table CHANGE COLUMN date date DATE

answered Nov 19, 2019 at 7:04

Deepa MG

1961 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bush · Accepted Answer · 2019-02-06 08:57:19Z

1

As MySql accepts the date in y-m-d format in date type column, you need to STR_TO_DATE function to convert the date into yyyy-mm-dd format for insertion in following way:

INSERT INTO table_name(today) 
VALUES(STR_TO_DATE('07-25-2012','%m-%d-%y'));

Similary, if you want to select the date in different format other than Mysql format, you should try DATE_FORMAT function

SELECT DATE_FORMAT(today, '%m-%d-%y') from table_name;

EDIT:

For altering the column to accept data in ur format,

mysql_query("UPDATE `Table` SET `date` = STR_TO_DATE(`date`, '%d-%m-%Y')");
mysql_query("ALTER TABLE `Table` CHANGE COLUMN `date` `date` DATE");

edited Feb 6, 2019 at 8:57

answered Feb 6, 2019 at 7:03

Bush

2611 silver badge11 bronze badges

4 Comments

Chetan Thapliyal Over a year ago

I am new to SQL so I may be wrong but the solution you have provided can be used when we are inserting Data into column one by one or Manually. How can I use it for a table with more than 100 or more rows OR for data in a CSV file?

Bush Over a year ago

@ChetanThapliyal I have made an edit in the answer. Kindly check if it serves the purpose.

P.Salmon Over a year ago

@Bhushan Shinde I don't see anything in your code which loads from a csv file.

Chetan Thapliyal Over a year ago

So I tried everything that I could and I end up using UPDATE and ALTER commands at last. Thanks @BhushanShinde and Thanks P.Salmon.

Collectives™ on Stack Overflow

Incorrect Date Value while loading CSV file to Table

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related