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