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

    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

        • 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
          file, and look for
          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.

          SQL Developer Team