Table of Contents
Ispirer Website
Ispirer Toolkit Overview
Free Trial
Formats
Overview
| Option Name | Description |
|---|
| Use datetime format | Specifies required datetime format |
| Save datetime fraction | Convert fraction part for datetime or no |
| Use date format | Specifies required date format |
| Use time format | Specifies required time format |
| Use ODBC float conversion | Convert numeric data to the text representation by ODBC driver or no |
| Allocate space for sign in data file | Specifies if allocation space for sign in data file is required or no |
| Empty strings to NULL | |
| Decimal point | Specifies a single character, which is used instead of a decimal point character for numeric values |
Use datetime format
This option specifies the format of the datetime datatypes in the text files.
Datetime data consist of valid date and time combinations. Depending on the database, this is the DATE data type in Oracle, TIMESTAMP data type in IBM DB2 and DATETIME data type in Microsoft SQL Server and Sybase.
A datetime format can be composed of one or more datetime format elements as listed below.
- YYYY - 4-digit year
- YY - Last 2 digits of year
- MM - 2-digit numeric abbreviation of month (01-12)
- MON - 3-symbol abbreviated name of month (JAN, FEB etc)
- DD - 2-digit day of month (01-31)
- HH - 2-digit hour of day (01-12)
- HH12 - 2-digit hour of day (01-12)
- HH24 - 2-digit hour of day (00-23)
- MI - 2-digit minute (00-59)
- SS - 2-digit second (00-59)
- F - Fraction of second. The number of F symbols represents the precision. For example, FFF for accuracy to the milliseconds and FFFFFF for accuracy to the microseconds.
For example, to get datetime values like 21-DEC-2002 21-21-00 set the datetime format DD-MON-YYYY HH24-MI-SS.
If no value is specified in this option, the default datetime format is used. By default, ODBC drivers convert datetime values using the ISO format YYYY-MM-DD HH24:MI:SS.FFF (the part FFF depends on database).
Save datetime fraction
Specifies that the datetime columns can contain the fraction part in the text file. Datetime data consist of valid date and time combinations.
Time can contain a fraction part. For example, IBM DB2 TIMESTAMP data type can contain microseconds, Microsoft SQL Server and Sybase DATETIME can keep milliseconds.
Possible values - Yes, No. Yes means that datetime values can contain the fraction part in the text files, No means that if the fraction part exists, it is not saved in the text file.
The default value is Yes unless the target database is Oracle.
If the target database is Oracle and the datetime datatypes are converted to the Oracle DATE data type, the default value is No. The Oracle DATE data type doesn't support fractional seconds.
If the target database is Oracle and the datetime datatypes are converted to the Oracle TIMESTAMP data type, the default value is Yes. The Oracle TIMESTAMP data type supports fractional seconds.
Note. This option is ignored if any value is specified in the option Use datetime format.
Use date format
This option specifies the format of the date datatypes in the text files.
A date is a three-part value (year, month and day). Not all databases have a data type that allows you to keep date values without a time part.
For example, such data type exists in IBM DB2 and MySQL (the DATE data type).
A date format can be composed of one or more date format elements as listed below.
- YYYY - 4-digit year
- YY - Last 2 digits of year
- MM - 2-digit numeric abbreviation of month (01-12)
- MON - 3-symbol abbreviated name of month (JAN, FEB etc)
- DD - 2-digit day of month (01-31)
For example, to get date values like 21-DEC-2002, set the datetime format DD-MON-YYYY.
If no value is specified in this option, the default date format is used. By default, ODBC drivers convert date values using the ISO format YYYY-MM-DD.
Use time format
This option specifies the format of the datetime datatypes in the text files.
Datetime data consist of valid date and time combinations. Depending on the database, this is the DATE data type in Oracle, TIMESTAMP data type in IBM DB2 and DATETIME data type in Microsoft SQL Server and Sybase.
A datetime format can be composed of one or more datetime format elements as listed below.
- HH - 2-digit hour of day (01-12)
- HH12 - 2-digit hour of day (01-12)
- HH24 - 2-digit hour of day (00-23)
- MI - 2-digit minute (00-59)
- SS - 2-digit second (00-59)
- F - Fraction of second. The number of F symbols represents the precision. For example, FFF for accuracy to the milliseconds and FFFFFF for accuracy to the microseconds.
For example, to get date values like 20:35:59, set the datetime format HH24:MI:SS.
If no value is specified in this option, the default date format is used. By default, ODBC drivers convert date values using the ISO format HH24:MI:SS.
Use ODBC float conversion
This option controls the conversion rule for numeric data.
If Yes is specified, numeric data (REAL, FLOAT, DOUBLE datatypes) are converted to the text representation by ODBC driver. Otherwise, SQLWays Wizard performs the conversion. The default is Yes. Possible values - Yes, No.
Allocate space for sign in data file
This option controls allocating space for sign in data file when target migration direction is Oracle.
The option is available only if OUTFORMAT=FIX.
If it is set to “Yes”, the tool increases POSITION parameter for TINYINT, SMALLINT, INTEGER and BIGINT data types to be able to store sign. Otherwise, “No” option value generates POSITION in accordance with original data type length.
Possible values - “Yes”, “No” or Empty.
Default value - “Yes”.
Decimal point
Choose “Tables” in the tree on the left, “Data Options” tab, “Format” combobox.
If you have any other questions regarding the usage of our tool, please contact our support team: support@ispirer.com