List:MySQL on Win32« Previous MessageNext Message »
From:Paul DuBois Date:December 10 2000 7:30pm
Subject:Re: dateformat for LOAD DATA INFILE
View as plain text  
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
Thread
dateformat for LOAD DATA INFILEChristian Fernsebner9 Dec
  • Re: dateformat for LOAD DATA INFILEPaul DuBois10 Dec
    • AW: dateformat for LOAD DATA INFILEChristian Fernsebner10 Dec