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