MySQL Forums
Forum List  »  Knowledge Base

Question OVER LOAD DATA INFILE and datetime datatype
Posted by: rajasekhar yakkali
Date: March 13, 2008 09:27AM

Hi,

I am using Datetime datatype with a default NOW() in the table and as i want to make use of LOAD DATA INFILE method to populate this table.
TABLE STRUCTURE
------------------
CREATE TABLE TEST (
create_date datetime default now()
);

mysql> insert into test values (DEFAULT);
Query OK, 1 row affected (0.06 sec)

mysql> select * from test;
+---------------------+
| create_date |
+---------------------+
| 0000-00-00 00:00:00 |
| 2008-03-12 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

mysql> insert into test values (CURRENT_DATE);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test;
+---------------------+
| create_date |
+---------------------+
| 0000-00-00 00:00:00 |
| 2008-03-12 00:00:00 |
| 0000-00-00 00:00:00 |
| 2008-03-12 00:00:00 |
+---------------------+
4 rows in set (0.00 sec)

mysql> desc test;
+-------------+----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------------------+-------+
| create_date | datetime | NO | | 0000-00-00 00:00:00 | |
+-------------+----------+------+-----+---------------------+-------+
1 row in set (0.01 sec)

mysql> insert into test values(now());
Query OK, 1 row affected (0.08 sec)

mysql> select * from test;
+---------------------+
| create_date |
+---------------------+
| 0000-00-00 00:00:00 |
| 2008-03-12 00:00:00 |
| 0000-00-00 00:00:00 |
| 2008-03-12 00:00:00 |
| 2008-03-12 19:03:18 |
+---------------------+
5 rows in set (0.00 sec)

ALL these operations above when performed at the command line through insert statements do work.

But none of them work if encoded into a csv and tried with LOAD DATA INFILE method

mysql> load data infile "C:/script_book/code/inventory/1.csv" into table test fi
elds terminated by ',' lines terminated by '\r\n';
ERROR 1292 (22007): Incorrect datetime value: 'DEFAULT' for column 'create_date'
at row 1


contents of csv in several attempts to simulate the process through LOADER method that were not fruitful..
===============================================
NULL
NULL

or

DEFAULT
DEFAULT

or

NOW()
NOW()



How to insert a bunch of records into the table with datetime as a datatype through LOAD DATA INFILE Method so that all the inserted records have the current values similar to those obtained with the function NOW() ?

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Question OVER LOAD DATA INFILE and datetime datatype
7214
March 13, 2008 09:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.