Offline Data Move DATEs
During the offline data move, scripts are created to use BCP to dump out your data in SQL Server or Sybase to .dat files. Other scripts are created to use Oracle SQL*Loader to load the .dat files into the Oracle tables.
Your SQL Server/ Sybase database will dump out the DATETIME and SMALLDATETIME values in formats which may not be recognized by Oracle. So you have to specify what the format mask is.
SQL Server / Sybase DataTypes
DATETIME has millisecond precision.
SMALLDATETIME has only second precision.
Oracle DataTypes
DATE has only second precision
TIMESTAMP has millisecond precision
You can specify their format mask in the migration preferences.
The preferences work like this
The best bit is that even if you map Sybases/ SQL Servers DATETIME to Oracles DATE (which is the default mapping). The TO_TIMESTAMP that happends in SQL*Loader will be implicitly converted to a DATE when inserted, and the milliseconds automatically truncated.
Here are some Defaults which you should specify in the Migration preferences
SQL Server
Timestamp Mask Preference = DATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss.ff3'
Date Mask Preference= SMALLDATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss'
Sybase
Timestamp Mask Preference = DATETIME in Sybase = 'Mon dd yyyy hh:mi:ss:ff3AM'
Date Mask Preference = SMALLDATETIME in Sybase = 'Mon dd yyyy hh:mi:ss'
You should check your .dat files to make sure the format is correct. You can regenerate the scipts after you change the preference. The formats are applied in the generated Oracle/*.ctl files.
Your SQL Server/ Sybase database will dump out the DATETIME and SMALLDATETIME values in formats which may not be recognized by Oracle. So you have to specify what the format mask is.
SQL Server / Sybase DataTypes
DATETIME has millisecond precision.
SMALLDATETIME has only second precision.
Oracle DataTypes
DATE has only second precision
TIMESTAMP has millisecond precision
You can specify their format mask in the migration preferences.
The preferences work like this
- The Timestamp Mask is applied to the DATETIME Sybase or SQL Server values
- The Date Mask is applied to the SMALLDATIME Sybase or SQL Server values.
The best bit is that even if you map Sybases/ SQL Servers DATETIME to Oracles DATE (which is the default mapping). The TO_TIMESTAMP that happends in SQL*Loader will be implicitly converted to a DATE when inserted, and the milliseconds automatically truncated.
Here are some Defaults which you should specify in the Migration preferences
SQL Server
Timestamp Mask Preference = DATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss.ff3'
Date Mask Preference= SMALLDATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss'
Sybase
Timestamp Mask Preference = DATETIME in Sybase = 'Mon dd yyyy hh:mi:ss:ff3AM'
Date Mask Preference = SMALLDATETIME in Sybase = 'Mon dd yyyy hh:mi:ss'
You should check your .dat files to make sure the format is correct. You can regenerate the scipts after you change the preference. The formats are applied in the generated Oracle/*.ctl files.