oracle to_date vs mysql date_format

Hello,
Try to migrate a subset of data from Oracle 9i to mysql 5.0. The mysql
migration toolkit does not seem to allow me to snarf a subset of a
table.
So I am doing an export from Oracle 91 which creates sql insert
statements.
These insert statements contain to_date('16-MAY-07','DD-MON-RR'), for
example. So I plan to do a search and replace in the sql file using
date_format instead of to_date.
but I have only succeeded in getting NULL as a result of passing in
date_format( '16-MAY-07', %d %M %y). the format string is always 'DD-
MON-RR' could someone advise on the correct date_format incantation
is? It is suppsoed to be all working tomorrow and I have to leave now
after an 11 hour day!

TIA

Ton

ton de w wrote:
> Hello,
> Try to migrate a subset of data from Oracle 9i to mysql 5.0. The mysql
> migration toolkit does not seem to allow me to snarf a subset of a
> table.
> So I am doing an export from Oracle 91 which creates sql insert
> statements.
> These insert statements contain to_date('16-MAY-07','DD-MON-RR'), for
> example. So I plan to do a search and replace in the sql file using
> date_format instead of to_date.
> but I have only succeeded in getting NULL as a result of passing in
> date_format( '16-MAY-07', %d %M %y). the format string is always 'DD-
> MON-RR' could someone advise on the correct date_format incantation
> is? It is suppsoed to be all working tomorrow and I have to leave now
> after an 11 hour day!
>
> TIA
>
> Ton

Two problems here.
1) You need to put the format string in quotes as it shows you in the
manual.
2) The manual explains that DATE_FORMAT takes an ISO date and returns it in
the format that you specify in the format string. It does not take a date in
some other format and return an ISO date. For that you need the function
STR_TO_DATE