Import External Data – Date

[andrewteg]

I have an external server creating CSV files which I need to copy and import into MySQL on a nightly basis. The process is going well but the date fields are M/D/Y. I am importing them as VARCHAR now since if I use DATE I get all 0000-00-00.

I am hoping someone can share a way to switch these types of dates into a true MySQL date field.

Thanks,

Andrew

[Khushboo]

Hi Andrew,

We tried your case, and import is working fine at our end.

These are the conversions of date datatype, which Import External Data does by default:

1. If in CSV date format is 'MM/DD/YYYY' then it will be changed to Timestamp in 'YYYY-MM-DD HH:MM:SS' format.

Eg: 02/12/2010

---

> 2010-02-12 00:00:00

2. If the format is 'DD/MM/YYYY', then it changes to Varchar.

Eg: 02/12/2010

---

> 2010-02-12

For any of the above two cases, if you change the datatype to Date (through Import External Data –> Column Mapping Section), it imports fine as Date in 'YYYY-MM-DD' format.

So, can you please provide us more details to solve your problem:

1. From which external server you are creating the CSV files?

2. Can you attach a sample CSV file which causes this problem?

Also, you can go through this link for more info on MySQL Date and Time datatypes:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html

Thank You.

Regards,

Khushboo

[peterlaursen]

Note that automatic conversion will work with the ODBC-based import – not when using LOAD DATA LOCAL INFILE syntax.

Also please tell what version you are using.  Recent versions have improved interface for the ODBC-based import.

[Author]

Posts