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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
