How to Import a CSV with Non-Standard Dates into MySQL?

Importing a CSV to MySQL with a Non-Standard Date Format

When importing a CSV file into MySQL, you may encounter dates in a format that is not compatible with the DATE data type, such as "31-Jan-2011." To resolve this issue, it is necessary to convert the date during the import process.

One approach is to use PHP to perform the conversion before inserting the data into a table. However, a more efficient option is to handle the conversion directly during the import process.

To achieve this, you can use the LOAD DATA INFILE statement and specify a custom date format. Here's an example:

In this example, the STR_TO_DATE function is used to convert the string "31-Jan-2011" into a DATETIME value. The %d-%b-%Y format specifier tells the function that the original date is in the format of day-month-year (DD-MM-YYYY).

By performing this conversion during the import process, you can ensure that the date is stored in the correct format in the database.

The above is the detailed content of How to Import a CSV with Non-Standard Dates into MySQL?. For more information, please follow other related articles on the PHP Chinese website!