problem with LOAD DATA INFILE for date format
| From: | Hendra | Date: | Fri, 25 Jan 2002 05:09:22 +0000 |
| Subject: | problem with LOAD DATA INFILE for date format | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-16159@lists.php.net to get a copy of this message | ||
Hi,
I have a sample data like below in text file (policy.txt).
"PolicyNo","DateOfBirth","PaidToDate"
"0003573607"," 9/25/1973"," 8/27/2001"
"0000708802","11/26/1959"," 5/25/1998"
"0002776507"," 3/19/1973","11/18/1999"
"0002776703"," 3/13/1969","11/18/1999"
Policy table structure:
+---------------+-------------+
| Field | Type |
+---------------+-------------+
| PolicyNumber | char(10) |
| DateOfBirth | date |
| PaidToDate | date |
+---------------+-------------+
When I do:
LOAD DATA INFILE "policy.txt" INTO TABLE Policy
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES;
All data in field DateOfBirth & PaidToDate become "0000-00-00"
I notice this is because mysql only accept date format in "yyyy-mm-dd".
However, in my text file, the date format is in "mm/dd/yyyy" format.
Any idea?
Thanks in advance.
Hendra