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;
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
