CONVERT

This function allows the conversion of fields into specific data types.

CONVERT( data_type, length, <field_name or expression> [, <format_spec>])

• Data_type: A string that represents the target data type, must be one of the following values.

  • Boolean
  • Currency
  • Date
  • Decimal
  • Geographic
  • Integer
  • Text

• Length: A value that represents the length of data to be created. This parameter will be ignored for BOOLEAN,CURRENCY,DECIMAL and INTEGER data types. Note however that for these data types a length of 1 must be entered.

• Field Name or Expression: An optional string that represents the field name or expression to be converted

• Format_spec: An optional parameter used in DATE, POTCODE and STRING conversions:

  • A string that defines the incoming format for a DATE.
  • A numeric value that specifies the POSTCODE format.
  • A string list that defines the format of the String to convert to.

Example: Convert the (integer) [Bingo] field to a Boolean, at the current time the field will converted to an integer field with -1 representing true and 0 representing false.

CONVERT(BOOLEAN,1,[QUESTIONAIRE.BINGO])

Example: Convert the (decimal) [Purchase Price] field to a currency data type.

CONVERT(CURRENCY, 1, [Purchase.Purchase Price])

Example: Convert the (text) [Date as text] field in the format DDMMYY to a date data type.

CONVERT( DATE, 1, [Purchase.DATE AS TEXT], "DDMMYY")

Example: Convert the (integer) [Numberinhousehold) field to a decimal data type.

CONVERT(DECIMAL,1,Household.NUMBERINHOUSEHOLD)

Example: Convert the (text) [Postcode as text] field to a geographic data type of length 10.

CONVERT(GEOGRAPHIC,10,[Household.POSTCODE AS TEXT])

Example: Convert the (decimal) [Numberinhousehold) field to an integer data type.

CONVERT(INTEGER,1,Household.NUMBERINHOUSEHOLD)

Example: Convert the (date) purchase date field to text field with a length of 10 in the format YYYY-MM-DD.

CONVERT(TEXT, 10, Purchase.PURCHASE_DATE, "yyyy-mm-dd")