ansaurus

Question

Answer 1

+2 A:

I don't believe it's possible without modifying the SQL.

STR_TO_DATE could be used in the insert statements to convert the strings, or you could try piping the file through sed and use a regex to spot dates and rework them, e.g. something like this would replace single quoted dates in your original dump with calls to str_to_date, and pipe the resulting transformed sql into mysql...

cat oracledump.sql |  sed "s/'[0-9][0-9]-\(JAN\|FEB\|MAR\|APR\|MAY\|JUN\|JUL\|AUG\|SEP\|OCT\|NOV\|DEC\)-[1-2][0-9][0-9][0-9]'/str_to_date(&, '%d-%b-%Y')/g" | mysql newdb

Paul Dixon 2009-01-01 11:35:24

I changed the end to > output.sql but all I got was the exact same file. The dates had not been changed.

thornate 2009-01-01 12:05:39

Maybe you need to tweak the regex for your data, I did test it on a sample file before posting though. Why note post a fragment of your SQL?

Paul Dixon 2009-01-01 12:12:22

Paul Dixon 2009-01-01 12:19:40

No, they were single quote dates. A full line example is as follows: INSERT INTO ord (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('710', '06-FEB-2002', '615', '01-FEB-2002', '107', '');

thornate 2009-01-01 12:37:25

Paul Dixon 2009-01-01 13:13:55

Hurrah! That worked great. I did have to amend it again after I realised that dates between 1 and 9 were printed with only one digit, but now it's all uploaded correctly. Thanks.

thornate 2009-01-01 13:25:49

ansaurus

tags:

views:

answers:

Change date format that MySQL reads

related questions