Lola J. Lee Beno wrote:
<snip>
Here's an example of one of the tables where the date was retained
successfully, with the string to the right of the decimal point being
trimmed:
1 Cobweb 2005-01-13 15:21:50.654149
2 Lace Weight 2005-01-13 15:21:50.654149
3 Sock 2005-01-13 15:21:50.654149
And the CREATE query for this table:
CREATE TABLE StandardWeightType (
standard_wt_type_id int UNSIGNED NOT NULL AUTO_INCREMENT,
standard_wt_desc varchar(50) NULL,
standard_wt_lud datetime NULL,
PRIMARY KEY (standard_wt_type_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
select * from standardweighttype:
+---------------------+------------------+---------------------+
| standard_wt_type_id | standard_wt_desc | standard_wt_lud |
+---------------------+------------------+---------------------+
| 1 | Cobweb | 2005-01-13 15:21:50 |
| 2 | Lace Weight | 2005-01-13 15:21:50 |
| 3 | Sock | 2005-01-13 15:21:50 |
| 4 | Fingering | 2005-01-13 15:21:50 |
| 5 | Baby | 2005-01-13 15:21:50 |
| 6 | Sport | 2005-01-13 15:21:50 |
| 7 | Double Knitting | 2005-01-13 15:21:50 |
| 8 | Worsted | 2005-01-13 15:21:50 |
| 9 | Aran | 2005-01-13 15:21:50 |
+---------------------+------------------+---------------------+
I'm not sure why it worked for this table and not the Yarn table.
I'll try out the proposed solutions later today. I do need to be able
to use the date column since I will be inserting new records into the
database via a web application that I will be developing; I am moving
the data over from a PostgreSQL database which I'd developed earlier
since I've decided to concentrate on MySQL for the time being.
I take it back. It is true that the all-zero datetime is what you get for
invalid input, so I jumped to the conclusion (sorry) that mysql was treating
your datetimes with decimals as invalid. Your reply prompted me to try it, and
I found, to my surprise, that mysql simply dropped the decimals.
So, why isn't this happening for the yarn data? My best guess is that there is
an extra tab right before the datetimes in yarn_date.txt. When you do the
import, does mysql report any warnings? With an extra tab in each row of your
3-line sample data, I get
Query OK, 3 rows affected, 6 warnings (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 6
at the end. If you have mysql 4.1 or higher, you can run
SHOW WARNINGS;
to get the details. With one extra tab per row, I got
+---------+------+---------------------------------------------------------------------------+
| Level | Code | Message
|
+---------+------+---------------------------------------------------------------------------+
| Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 1
|
| Warning | 1262 | Row 1 was truncated; it contained more data than there were
input columns |
| Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 2
|
| Warning | 1262 | Row 2 was truncated; it contained more data than there were
input columns |
| Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 3
|
| Warning | 1262 | Row 3 was truncated; it contained more data than there were
input columns |
+---------+------+---------------------------------------------------------------------------+
6 rows in set (0.00 sec)
So, take a look at yarn_date.txt and let us know.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
