CODEWITHSUNDEEP
mysqloracle-databasedatetimecsvtimestamp
user3912872
user3912872

Reputation: 1

Oracle to Mysql timestamp data is not importing

I am trying to load data from CSV (which is generated by oracle table) to MySQL table.

Sample CSV file content:

ID, Number, CreationTimeStamp, ModifiedTimestamp

64282,44000000,26-JUN-13 03.18.48.235000 PM,26-JUN-13 03.18.55.929157 PM

64283,44000000,26-JUN-13 03.25.45.013000 PM,26-JUN-13 03.25.52.015622 PM

64286,40000000,27-JUN-13 03.14.33.198000 PM,27-JUN-13 03.14.39.090887 PM

When i load data into MySQL (Using LOAD DATA INFILE...)i get zeros in CreationTimeStamp and ModifiedTimestamp fields.

MySQL after loading from CSV:

| 64282 | 44000000 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

| 64283 | 44000000 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

| 64286 | 40000000 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

What is the data type i should use to import timestamp values? What is the equivalent datatype for Oracle's TIMESTAMP datatype in MySQL?

Upvotes: 0

Views: 1808

Answers (1)

catalinetu
catalinetu

Reputation: 660

First you import data as VARCHAR then you need to convert it like this:

SELECT STR_TO_DATE('26-JUN-13 03.18.55.929157','%d-%M-%y %H.%i.%s');

Upvotes: 1

Related Questions