CODEWITHSUNDEEP
mysqloracle-databaseformattimestampdatabase-migration
Jake
Jake

Reputation: 11

Oracle to MySQL timestamp conversion in MySQL insert statement

I've already exported data from an Oracle 11g instance as insert statements. They need to go into a MySQL 5.5 instance. Having trouble with the TIMESTAMP conversion; I know I can use the TO_TIMESTAMP function inline with INSERT statements in MySQL; however, am unsure as to the correct flags to use. As follows below, in linear order, is: the Oracle timestamp statement and my attempt at a MySQL compatible statement. Obviously it's not working (in that MySQL states there's a syntax error).

Oracle: 

TO_TIMESTAMP('12/22/2015 5:08:59.245837 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM')

MySQL: 

TO_TIMESTAMP('12/22/2015 5:08:59.245837 PM','%m/%d/%Y %h:%i:%s')

What am I missing beyond flags for microseconds and AM/PM?

Upvotes: 1

Views: 6073

Answers (3)

Mohannd
Mohannd

Reputation: 1438

if you want the date and the timestamp of oracle to be compatible with MySql just alter you session 

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'

it is better to alter session

Upvotes: 0

Rocky Menezes
Rocky Menezes

Reputation: 19

FOR MYSQL: STR_TO_DATE('2/6/2015 5:20:43.000000 AM','%c/%d/%Y %l:%i:%s.%f %p')

Upvotes: 1

alexanderarda
alexanderarda

Reputation: 400

assumed you export from SQL Developer.

before you export the whole data, change your datetime select query with :

TO_CHAR(COLUMN_NAME, 'YYYY-MM-DD HH24:MI:SS') as XXX

it will produce datetime with MySQL format. so when you get the insert query, you can run directly at MySQL

Upvotes: 0

Related Questions