I have a bunch of data where one of the columns is a date field. Here's
a sample of the data that I have:
1 4 14 150 1 0 0 0 1
0 2005-01-15 10:15:42.41837
2 8 15 120 1 0 0 0 2
0 2005-01-15 10:22:37.756594
3 6 16 350 2 0 0 0 4
0 2005-01-15 10:27:26.559838
When I run this query:
LOAD DATA LOCAL INFILE
'/users/lolajl/documents/development/knitlib/datafiles/yarn_date.txt'
INTO TABLE yarn
(yarn_id, standard_wt_type_id, brand_id, yarn_yardage, mfr_id,
yarn_meters, yarn_putup, yarn_wt_g, yarn_wt_oz, yarn_discontinued_flg,
yarn_lud);
The dates all get set to:
0000-00-00 00:00:00
As you can see:
| 1 | 4 | 14 | 150 | 1 | 0 | 0 | 0 | 1 | 0 |
0000-00-00 00:00:00 |
| 2 | 8 | 15 | 120 | 1 | 0 | 0 | 0 | 2 | 0 |
0000-00-00 00:00:00 |
| 3 | 6 | 16 | 350 | 2 | 0 | 0 | 0 | 4 | 0 |
0000-00-00 00:00:00 |
(I've deleted as many spaces as I could so as to make this more readable.)
When I delete the numbers to the left of the decimal point in the date
field in yarn_date.txt, it still gets set to the above format. I tried
setting the yarn_lud column to NULL and still the same thing.
Here is the query that creates this table:
CREATE TABLE Yarn (
yarn_id int UNSIGNED NOT NULL AUTO_INCREMENT,
standard_wt_type_id int UNSIGNED NULL,
brand_id int UNSIGNED NULL,
yarn_yardage int NULL,
mfr_id int UNSIGNED NULL,
yarn_meters int NULL,
yarn_putup varchar(35) NULL,
yarn_wt_g int NULL,
yarn_wt_oz int NULL,
yarn_discontinued_flg tinyint NULL,
yarn_lud datetime NULL,
PRIMARY KEY (yarn_id),
CONSTRAINT `fk_yarn_brandid` FOREIGN KEY (brand_id)
REFERENCES Brand (brand_id),
CONSTRAINT `fk_yarn_mfrid` FOREIGN KEY (mfr_id)
REFERENCES Manufacturer (mfr_id),
CONSTRAINT `fk_yarn_stwgttypid` FOREIGN KEY (standard_wt_type_id)
REFERENCES StandardWeightType
(standard_wt_type_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
The odd thing is that I have three tables with a column for the date and
the dates are retained properly. What could be causing the dates to be
converted to the 0000-00-00 format automatically?
--
Lola - mailto:[EMAIL PROTECTED]
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
Freedom is not free.
I'm in Bowie, MD, USA, halfway between DC and Annapolis.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
