0

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.

Chetan Thapliyal
  • 3
  • 1
  • 7
  • [https://stackoverflow.com/questions/11641096/error-while-inserting-date-incorrect-date-value](https://stackoverflow.com/questions/11641096/error-while-inserting-date-incorrect-date-value) this may help you. – Santosh D. Feb 06 '19 at 07:03
  • 1
    How are you loading the csv file? using load data infile or mysqlwrokbench import wizard? – P.Salmon Feb 06 '19 at 07:08
  • By import wizard. – Chetan Thapliyal Feb 06 '19 at 07:11
  • 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 A Feb 06 '19 at 07: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 https://stackoverflow.com/questions/8163079/importing-a-csv-to-mysql-with-different-date-format. – P.Salmon Feb 06 '19 at 07:26
  • I tried LOAD DATA query and after resolving lots of errors, I am stuck at "Error Code: 1148. The used command is not allowed with this MySQL version " – Chetan Thapliyal Feb 06 '19 at 13:55
  • Thanks @SalmanA I end up doing exactly that. Imorted the date as VARCHAR and then Updated and Altered the Table. Learned a lot of things in 1 Day. – Chetan Thapliyal Feb 07 '19 at 07:25

2 Answers2

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");
Bush
  • 261
  • 1
  • 11
  • 1
    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? – Chetan Thapliyal Feb 06 '19 at 08:47
  • @ChetanThapliyal I have made an edit in the answer. Kindly check if it serves the purpose. – Bush Feb 06 '19 at 08:58
  • 1
    @Bhushan Shinde I don't see anything in your code which loads from a csv file. – P.Salmon Feb 06 '19 at 09:04
  • So I tried everything that I could and I end up using UPDATE and ALTER commands at last. Thanks @BhushanShinde and Thanks P.Salmon. – Chetan Thapliyal Feb 07 '19 at 07:21
1

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

Deepa MG
  • 188
  • 1
  • 15