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]

Reply via email to