1 Reply Latest reply: Nov 20, 2012 2:44 PM by Gary Graham-Oracle RSS

    MySQL Migration - Default values of DATE columns

    Ivan Saenz
      Hi all,

      I'm migrating a MySQL database. Most DATE or TIMESTAMP columns in MySQL have a default value of 0000-00-00 00:00:00. This value is converted to to_date('01-JAN-70 00:00:00', 'dd-MON-yy hh24:mi:ss') even if my SQL Developer interface and NLS parameters are set to SPANISH.

      This produces errors when executing the generated script because JAN is not a valid month in spanish. I think migration process should generate to_date('01-01-70 00:00:00', 'dd-MM-yy hh24:mi:ss') instead.

      Any way to get the script correctly generated?

      Using SQL Developer version 3.2.20.09

      Thanks
        • 1. Re: MySQL Migration - Default values of DATE columns
          Gary Graham-Oracle
          Hi Ivan,

          Unfortunately it appears the value is hard-coded. If you browse your SQL Developer installation's
          .../sqldeveloper/sqldeveloper/extensions/oracle.sqldeveloper.migration.mysql.jar
          file, and look for
          /oracle/dbtools/migration/workbench/plugin/mysql/MySQLDefaultValuesMappingRules.xml
          you will see it inside there.

          So you are saying that changing the locale settings in sqldeveloper.conf to English/US is not an option for you? Something like:
          AddVMOption -Duser.language=en
          AddVMOption -Duser.country=US
          Otherwise you might try verifying that all your NLS settings are consistent, both for Migration and the Database. That is:
          Tools > Preferences > Database > NLS
          Tools > Preferences > Migration > Data Move Options (either Masks or the User-Defined Custom Preferences By Source Type)
          Tools > Preferences > Migration > Translators
          This is not my area of expertise. I think some settings depend whether your migration is on- or off-line. If nothing mentioned here works for you, just post again on this thread and perhaps someone more knowledgeable will respond.

          Regards,
          Gary
          SQL Developer Team