List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:March 26 2006 3:20am
Subject:Re: Date Field Reverting to 0000-00-00 Format
View as plain text  
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

Thread
Date Field Reverting to 0000-00-00 FormatLola J. Lee Beno26 Mar
  • Re: Date Field Reverting to 0000-00-00 FormatMichael Stassen26 Mar
    • Re: Date Field Reverting to 0000-00-00 FormatLola J. Lee Beno26 Mar
      • Re: Date Field Reverting to 0000-00-00 FormatMichael Stassen27 Mar
        • Re: Date Field Reverting to 0000-00-00 FormatLola J. Lee Beno27 Mar