Database Reference
In-Depth Information
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
Recipe 11.1
,
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 (see
Recipe 6.3
) to interpret
the input dates:
mysql>
LOAD DATA LOCAL INFILE 'newdata.txt'
->
INTO TABLE t (name,@date,value)
->
SET date = STR_TO_DATE(@date,'%m/%d/%y');
With the
%y
format specifier in
STR_TO_DATE()
, MySQL converts the two-digit years to
four-digit years automatically, so the original
MM/DD/YY
values end up as ISO values in
CCYY-MM-DD
format. The resulting data after import looks like this:
+-------+------------+-------+
| name | date | value |
+-------+------------+-------+
| name1 | 1999-01-01 | 38 |
| name2 | 2000-12-31 | 40 |
| name3 | 2013-02-28 | 42 |
| name4 | 2018-01-02 | 44 |
+-------+------------+-------+
This procedure assumes that MySQL's automatic conversion of two-digit years to four
digits produces the correct century values. This means that the year part of the values
must correspond to years in the range from 1970 to 2069. If that's not true, you must
convert the year values some other way. (For some ideas, see
Recipe 12.11
.)
If the dates are not in a format that
STR_TO_DATE()
can interpret, perhaps you can write
a stored function to handle them and return ISO date values. In that case, the
LOAD
DATA
statement looks like this, where
my_date_interp()
is your stored function name:
mysql>
LOAD DATA LOCAL INFILE 'newdata.txt'
->
INTO TABLE t (name,@date,value)
->
SET date = my_date_interp(@date);
12.14. Exporting Dates Using Non-ISO Formats
Problem
You want to export date values using a format other than MySQL's default ISO (
CCYY-
MM-DD
) format. This might be a requirement when exporting dates from MySQL to
applications that don't use ISO format.
