List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 20 2001 1:53am
Subject:Re: About the date
View as plain text  
On Wed, Sep 19, 2001 at 06:55:00PM -0700, Ida Sze wrote:
> Dear Sir/Madam,
>   I'd like to upload data into a table from a text
> file. Inside the file, there is a field about the
> date. It is in MM/DD/YY format. But inside the MySQL
> manual I can't find any information about it, so can
> you tell me what can
> I do?
>   Thank you!
> 
> Best Regrads,
> 
> Ida

1) Reformat your data into YY-MM-DD format before loading it into the
table.

2) Load it into a character column, then bust it apart and put the
pieces back in the right order.  Here's an example.

Suppose you want to load this data:

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

And you want to load it into the following table:

CREATE TABLE tmp
(
	name	CHAR(20),
	date	DATE,
	value	INT
)

Do something like this:

ALTER TABLE tmp ADD cdate CHAR(8);
LOAD DATA LOCAL INFILE 'newdata.txt' INTO TABLE tmp (name,cdate,value);

UPDATE tmp
SET date = CONCAT(RIGHT(cdate,2),'-',LEFT(cdate,2),'-',MID(cdate,4,2));

ALTER TABLE tmp DROP cdate;
Thread
Re: About the dateIda Sze20 Sep
  • Re: About the datePaul DuBois20 Sep