Changing a Field's Data Type

Spectrum™ Technology Platform automatically changes field data types as needed using the type conversion settings specified in Management Console, or the dataflow's type conversion options specified in Enterprise Designer. In most situations you do not need to manually change field data types because any necessary data type conversions are handled automatically. However, in cases where a stage is unable to convert incoming data to the necessary data type, you may need to manually change the data type in the upstream channel.

There are only a few possible type conversions that you can perform manually. Those are:

  • Polygon and MultiPolygon types can be converted to and from a geometry type.
  • Date, time, and datetime data types can be converted to and from a string type.

To manually change a field's data type, follow this procedure.

  1. In Enterprise Designer, double-click the channel where you want to change the field's data type. A channel is the line that connects two stages on the canvas.
  2. Click the small square button next to the data type that you want to change.
    Note: If a small square button is not visible next to the data type, then manual data type conversion is not available for your situation.
  3. For date, time, and datetime data types, do the following:
    Note: Only the appropriate options will be displayed depending on the data type chosen.
    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 107 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 107 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.
  4. Click OK.

    The color of the data type name changes to green.

  5. Click OK again to save the change.