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

Type C D T N F I B Y L M G V Q W

C

F

P

P

P

P

P

P

P

P

F

N

F

F

F

D

F

F

F

N

N

N

N

N

N

F

N

F

N

N

T

F

P

F

N

N

N

N

N

N

F

N

F

N

N

N

F

N

N

F

F

P

F

P

P

F

N

F

N

N

F

F

N

N

F

F

P

F

P

P

F

N

F

N

N

I

F

N

N

F

F

F

F

F

P

F

N

F

N

N

B

P

N

N

P

P

P

F

P

P

F

N

P

N

N

Y

F

N

N

P

P

P

F

F

P

F

N

F

N

N

L

F

N

N

F

F

F

F

F

F

F

N

F

N

N

M

P

P

P

P

P

P

P

P

P

F

N

P

P

F

G

N

N

N

N

N

N

N

N

N

N

F

N

N

F

V

P

P

P

P

P

P

P

P

P

F

N

F

P

P

Q

P

N

N

N

N

N

N

N

N

F

N

P

F

F

W

P

N

N

N

N

N

N

N

N

F

F

P

P

F

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

Reference

Data Conversion Functions

Concepts

Data Type Conversion Control

Other Resources

Functions
Data and Field Types