CAST( ) Function

Converts an expression from one data type to another.

CAST(eExpression AS cDataType [(nFieldWidth [, nPrecision])]
   [NULL | NOT NULL])

Parameters

• eExpression
  Specifies a data expression, usually in a SQL statement, that you want to convert to another data type. eExpression can be a field, calculated field, or other type of expression.

• cDataType
  Specifies the valid name of or letter representing the target data type name. cDataType can also be an expression that evaluates to a valid data type name or letter representing a valid data type.

  Note

  If you specify an expression for cDataType, you must enclose the expression you specify for cDataType with parentheses.

  Some field data types require that you specify a value for nFieldWidth, nPrecision, or both. The following table lists values for cDataType and whether nFieldWidth and nPrecision apply.

  cDataType	Data type	nFieldWidth	nPrecision
  W, Blob	Blob	No	No
  C, Char, Character	Character	Yes	No
  Y, Currency	Currency	No	No
  D, Date	Date	No	No
  T, DateTime	DateTime	No	No
  B, Double	Double	No	Yes
  F, Float	Floating	Yes	Yes
  G, General	General	No	No
  I, Int, Integer	Integer	No	No
  L, Logical	Logical	No	No
  M, Memo	Memo	No	No
  N, Num, Numeric	Numeric	Yes	Yes
  Q, Varbinary	Varbinary	Yes	No
  V, Varchar	Varchar	Yes	No

• nFieldWidth
  Specifies the field width for certain data types. Visual FoxPro disregards nFieldWidth for the following data types: D, T, I, Y, L, M, G, and W. If nFieldWidth is not specified, default values are used, for example, as defined by the CREATE TABLE command.

• nPrecision
  Specifies the number of decimal place precision for certain data types. Visual FoxPro disregards nPrecision for the following data types: C, D, T, I, Y, L, M, G, V, Q, and W.

  If nPrecision is not included for the Numeric or Float data type, the default is zero (0), which is no decimal places. If nPrecision is not included for the Double data type, nPrecision defaults to the number of decimal place precision set by the SET DECIMALS command. If nPrecision is not specified for any other data type, default values are used, for example, as defined by the CREATE TABLE command.

• NULL | NOT NULL
  Specifies whether to permit null values in the field.

  Note

  If one or more fields can contain null values, the maximum number of fields the table can contain is reduced from 255 to 254.

  If NULL or NOT NULL is not specified, it is inherited from the expression.

Remarks

The following table shows conversions supported by the CAST( ) function. The vertical axis lists the source data type, and the horizontal axis lists the target data type. The following letters describe the level of conversion supported:

• F - Full conversion without loss of data

• P - Partial conversion with possible loss of data

• N - Not supported

If you specify decimal precision, conversions between numeric data types, for example, Numeric, Float, Currency, and Double, to Integer data type might result in loss of precision.

For conversions to Character, Varchar, and Varbinary data types, if the specified field width is less than that of the original data, Visual FoxPro truncates values to fit the field width. For conversions to Character data type, if the specified width is greater than that of the original data, Visual FoxPro pads the field with spaces to the right of the value.

Example

The following example uses the SQL SELECT command to retrieve data from the Product_ID field in the Products table from the Visual FoxPro sample database, TestData.dbc. The example uses the CAST( ) function to convert the results returned from the expression unit_Cost * in_Stock, which have Currency type, as a value with Numeric type with a field width of 8 and a decimal precision of 2.

CLEAR ALL
OPEN DATABASE HOME(2) + 'Data\Testdata.dbc'
SELECT Product_ID, CAST(unit_Cost * in_Stock AS Numeric(8,2)) FROM Products

See Also

Tasks

How to: Choose Data Types

Concepts

Data Type Conversion Control

Reference

Data Conversion Functions

Other Resources

Functions

Data and Field Types