Setting Data Type Conversion Defaults

Data type conversion occurs when a dataflow automatically converts a field to the data type needed by a stage. Data type conversion also occurs when within some stages. For example, in Read from DB you can choose to have a field use the string data type even though the source data is in a numeric data type. The data is converted into the string data type when it is read into the dataflow.

There are two settings that you can use to control data type conversions. First, there are settings that determine how to format numeric, date, and time data converted into a string. For example, you may want date data that is converted into a string to be represented in the format mm/dd/yyyy rather than dd/mm/yyyy. The other setting controls what should happen if the system is unable to convert a field from one data type to another.

You can set the default data type conversion settings for your system in Management Console. You can override the default formats for individual dataflows in Enterprise Designer.

To set the default data type conversion options for your system, follow this procedure.

Open the Management Console.
Go to Flows > Defaults.
Click Data Type Conversions.
Specify the formats that you want to use for date and time data that is converted to a string. When the data or time is converted to a string, the string will be in the format you specify here.
1. In the Locale field, select the country whose format you want to use for dates converted to a string. Your selection will determine the default values in the Date, Time, and DateTime fields. Your selection will also determine the language used when a month is spelled out. For example, if you specify English the first month of the year would be "January" but if you specify French it would be "Janvier."
2. In the Date field, select the format to use for date data when it is converted to a string. A list of the most commonly used formats for the selected locale is provided.
  
  For example, if you choose the format M/D/YY and a date field contains 2012-3-2, that date data would be converted to the string 3/2/12.
3. In the Time field, select the format to use for time data when it is converted to a string. A list of the most commonly used formats for the selected locale is provided.
  
  For example, if you choose the format h:mm a and a time field contains 23:00, that time data would be converted to the string 11:00 PM.
4. In the DateTime field, select the format to use for fields containing the DateTime data type when converted to a string. A list of the most commonly used formats for the selected locale is provided.
  
  For example, if you choose the format M/d/yy h:mm a and a DateTime field contains 2012-3-2 23:00, that DateTime data would be converted to the string 3/2/12 11:00 PM.
5. In the Whole numbers field, select the formatting you want to use for whole numbers (data types float and double).
  
  For example, if you choose the format #,### then the number 4324 would be formatted as 4,324.
  
  Note: If you leave this field blank, numbers will be formatted in the same way they were in Spectrum™ Technology Platform 8.0 and earlier. Specifically, no thousands separator is used, the dot (".") is used as the decimal separator, numbers less than 10^-3 or greater than or equal to 10⁷ are shown in scientific notation, and negative numbers have a minus sign ("-") in front of them. Also note that if you leave this field blank, numbers that use the bigdecimal data type will always be in the format #,###.000.
6. In the Decimal numbers field, select the formatting you want to use for numbers that contain a decimal value (data types integer and long).
  
  For example, if you choose the format #,##0.0# then the number 4324.25 would be formatted as 4,324.25.
  
  Note: If you leave this field blank, numbers will be formatted in the same way they were in Spectrum™ Technology Platform 8.0 and earlier. Specifically, no thousands separator is used, the dot (".") is used as the decimal separator, numbers less than 10^-3 or greater than or equal to 10⁷ are shown in scientific notation, and negative numbers have a minus sign ("-") in front of them. Also note that if you leave this field blank, numbers that use the bigdecimal data type will always be in the format #,###.000.
You can also specify your own date, time, and number formats if the ones available for selection do not meet your needs. To specify your own date or time format, type the format into the field using the notation described in Date and Time Patterns. To specify your own number format, type the format into the file using the notation described in Number Patterns.
Under Null handling, choose whether to perform type conversion if a field contains a null value. If you select any of the following options, either the dataflow or the record containing the null value will fail based on your selection in the Failure handling field.

Fail null string

Fail the dataflow or record if type conversion is needed on a string field that contains a null value.

Fail null Boolean

Fail the dataflow or record if type conversion is needed on a Boolean field that contains a null value.

Fail null numeric

Fail the dataflow or record if type conversion is needed on a numeric field that contains a null value. Numeric fields include double, float, long, integer, and Big Decimal fields.

Fail null date

Fail the dataflow or record if type conversion is needed on a date field that contains a null value. This includes date, time, and DateTime fields.
In the Failure handling field, specify what to do when a field's value cannot be automatically converted to the data type required by a stage.

Fail the dataflow

If a field cannot be converted the dataflow will fail.

Fail the record

If a field cannot be converted the record will fail but the dataflow will continue to run.

Initialize the field using default values

If a field cannot be converted the field's value is replaced with the value you specify here. This option is useful if you know that some records contain bad data and you want to replace the bad data with a default value. Specify a value for each data type.