CODEWITHSUNDEEP
mysqlmysql-workbench
Chetan Thapliyal
Chetan Thapliyal

Reputation: 3

Incorrect Date Value while loading CSV file to Table

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.

Upvotes: 0

Views: 5911

Answers (2)

Deepa MG
Deepa MG

Reputation: 196

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

Upvotes: 2

Bush
Bush

Reputation: 261

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");

Upvotes: 1

Related Questions