0

I have imported a csv file into my database created in phpmyadmin using sql.

Server version: 5.6.37 - MySQL Community Server (GPL

I have a column for dates which has a datatype of date. The data displayed on webpage for all the dates are as 0000-00-00. However in the csv file the format is 12/2/2016. Could any one please help how could I resolve this issue?

  • 1
    What are the values stored in MySQL server? – McNets Oct 16 at 20:00
  • 1
    Looks like you failed to import dates properly, so the server returns the default "zero" values. – mustaccio Oct 16 at 20:11
  • From excel convert it into yyyy-mm-dd (2016-02-12) format then try to import. – James Oct 17 at 4:30
  • .csv file the format is 12/2/2016. Wrong format. You must see a lot of warnings - why does you ignore them? Use UDV to convert it to the format clear for server (YYYY-MM-DD) during import. – Akina Oct 17 at 4:51
2

Use user-defined variable for to convert the date value from dd\mm\YYYY format to the date/datetime datatype value during import:

LOAD DATA INFILE 
....
(field1, field2, ..., @date, ...)
SET datefield = STR_TO_DATE(@date, '%d\/%m\/%Y');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.