Change the date format that MySQL reads
I have a batch file that was generated for an Oracle database that I am currently trying to import into a MySQL database. I've had to deal with all sorts of inconsistencies between them. The file has all dates as "17 -NOV-1981" and not "1981-11-17" as MySQL expects.
The ALTER SESSION command was set in the batch file set nls_date_format = 'DD-MON-YYYY'; to set the date format. Is there an equivalent in MySQL to get it to accept dates as-is without having to basically edit the file?
source to share
I don't believe this is possible without changing the SQL.
STR_TO_DATE can be used in insert statements to convert strings, or you can try to connect a file via sed and use a regex to determine dates and re-process them, for example something like this will replace the single quotes in your original dump with str_to_date calls and pop the resulting converted sql to 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
source to share