At 12:33 AM +0100 12/9/00, Christian Fernsebner wrote:
>Hello List,
>is it possible to train the function LOAD DATA INFILE to import dates like
>'dd/mm/yyyy' and not 'yyyy-dd-mm' ?
>greetings Chris
No. Well, you could read it into a CHAR field, then add a DATE field
and run an UPDATE to reformat the CHAR into DATE format and put it in the
DATE field, then drop the CHAR field.
Suppose you have dates in a file like this:
10/02/1970
1/2/1999
You could load and reformat like this:
# - Create a file with CHAR and DATE columns
# - Load some mm/dd/yyyy data into the CHAR column
# - Reformat the CHAR column into the DATE column
# - Drop the CHAR column
DROP TABLE IF exists dates;
CREATE TABLE dates
(
c_date CHAR(10), # date as CHAR column
d_date DATE # date as DATE column
);
LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE dates (c_date);
SELECT * FROM dates;
UPDATE dates SET d_date =
CONCAT(
SUBSTRING_INDEX(c_date,'/',-1), # year part
'-',
SUBSTRING_INDEX(c_date,'/',1), # month part
'-',
SUBSTRING_INDEX(SUBSTRING_INDEX(c_date,'/',2),'/',-1) #day part
);
ALTER TABLE dates DROP c_date;
SELECT * FROM dates;
Nope, it ain't pretty! :-)
Or you could just preprocess the data file to reformat the date
before loading the file.
--
Paul DuBois, paul@stripped
