MySQL Cookbook, 2nd Edition

Importing Non-ISO Date Values

Problem

Date values to be imported are not in the ISO (CCYY-MM-DD) format that MySQL expects.

Solution

Use an external utility to convert the dates to ISO format before importing the data into MySQL (cvt_date.pl is useful here). Or use LOAD DATA’s capability for preprocessing input data before it gets loaded into the database.

Discussion

Suppose that you have a table that contains three columns, name, date, and value, where date is a DATE column requiring values in ISO format (CCYY-MM-DD). Suppose also that you’re given a datafile newdata.txt to be imported into the table, but its contents look like this:

name1   01/01/99    38
name2   12/31/00    40
name3   02/28/01    42
name4   01/02/03    44

Here the dates are in MM/DD/YY format and must be converted to ISO format to be stored as DATE values in MySQL. One way to do this is to run the file through the cvt_date.pl script shown earlier in the chapter:

%cvt_date.pl --iformat=us --add-century newdata.txt > tmp.txt

You can then load the tmp.txt file into the table. This task also can be accomplished entirely in MySQL with no external utilities by using SQL to perform the reformatting operation. As discussed in Preprocessing Input Values Before Inserting Them, LOAD DATA can preprocess input values before inserting them. Applying that capability to the present problem, the date-rewriting LOAD DATA statement looks like this, using the STR_TO_DATE() function (Changing MySQL’s Date Format) to interpret the input dates:

mysql>LOAD DATA ...