Lola J. Lee Beno wrote:
> 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
0000-00-00 00:00:00 is what you get when you try to insert an invalid datetime.
Valid datetimes don't have decimals. See the manual for details
<http://dev.mysql.com/doc/refman/5.0/en/datetime.html>.
> 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.
Ummm, if you delete the numbers to the *left* of the decimal point,
"2005-01-15 10:15:42.41837" will turn into ".41837", which is still not a valid
datetime. You need to delete the numbers to the *right* of the decimal point
(which I expect you meant), *and* you need to delete the decimal point. Then
you'll have a valid datetime (e.g. "2005-01-15 10:15:42").
Alternatively, you can import your data into a table with a string column in the
place of yarn_lud. Something like
datestring CHAR(28)
should do. Then you can set yarn_lud to
LEFT(datestring, 19)
or, if necessary,
LEFT(datestring, LOCATE('.', datestring) - 1)
Adding NULL to the definition of yarn_lud only means that NULLs are allowed. It
has no bearing on correct datetime format, nor on the default value for invalid
datetimes.
> 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?
I'm not sure what you think is odd about datetime columns behaving as expected,
but I am sure you're getting the zero datetime because of invalid input.
Michael
