CONVERT

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

CONVERT( data_type, <field_name or expression>)

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

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

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

Example: convert the integer 'Bingo' field to a Boolean, at the current time the field will convert to an integer field with -1 representing true and 0 representing false.

CONVERT(BOOLEAN,[QUESTIONNAIRE].[BINGO])

Example: convert the decimal 'Purchase Price' field to a currency data type.

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

Example: convert the text 'Date as text' field in the format MMDDYY to a date data type.

CONVERT( DATE, [Purchase].[DATE AS TEXT])

Example: convert the integer 'Numberinhousehold' field to a decimal data type.

CONVERT(DECIMAL, [Household].[NUMBERINHOUSEHOLD])

Example: convert the decimal 'Numberinhousehold' field to an integer data type.

CONVERT(INTEGER, [Household].[NUMBERINHOUSEHOLD])

Example: convert the date 'purchase date' field to text field.

CONVERT(TEXT, [Purchase].[PURCHASE_DATE])