CAST - SQL Function

The CAST function converts a specified value to the specified data type. This function is particularly useful when you need to explicitly define data types in your queries.

Usage

The CAST function can be utilized in:

The fields list of a SELECT statement.
The predicate of a WHERE clause.

Syntax

CAST({Field: Variant AS DataType: String})

Parameters

Parameter	Description
Field	(Required) The field or value to be converted.
DataType	(Required) The target data type to which the field should be converted. Supported Data Types AMOUNT BOOLEAN BYTE CHAR(X): A character string of length X. CHARACTER(X): Synonym for CHAR(X). DATE DATETIME DECIMAL DISCOUNT FLOAT INT INTEGER LARGEINT MONEY NCHAR(X): A Unicode character string of length X. NUMERIC SMALLINT TIME TIMESTAMP VARCHAR(X): A variable-length character string of length X. WORD Note: Replace X with the desired length for character-based data types. The maximum length for X is 8192 characters.

Parameter

Description

Field

(Required) The field or value to be converted.

DataType

(Required) The target data type to which the field should be converted.

Supported Data Types

AMOUNT
BOOLEAN
BYTE
CHAR(X): A character string of length X.
CHARACTER(X): Synonym for CHAR(X).
DATE
DATETIME
DECIMAL
DISCOUNT
FLOAT
INT
INTEGER
LARGEINT
MONEY
NCHAR(X): A Unicode character string of length X.
NUMERIC
SMALLINT
TIME
TIMESTAMP
VARCHAR(X): A variable-length character string of length X.
WORD

Note: Replace X with the desired length for character-based data types. The maximum length for X is 8192 characters.

Examples:

Example 1: Casting to a DATE

SELECT CAST(DocumentDate as DATE)
FROM INHEAD

This example converts the DocumentDate field to the DATE data type.

Example 2: Casting to a Variable-length String

SELECT CAST(INHEAD.DocumentID as VARCHAR(20))

FROM INHEAD

This example converts the DocumentID field to a variable-length string of up to 20 characters.

Example 3: Casting to a Unicode-Supported String

SELECT CAST('' as NCHAR(200)) AS EmptyUnicodeString

FROM INHEAD

This example creates an empty Unicode supported string with a length of 200 characters.

Additional Notes

Use CAST to ensure compatibility and correctness in data type transformations.
When casting to character types, be mindful of the specified length (X) to avoid truncation or errors.
Use NCHAR for wide character strings that require Unicode support.

By leveraging the CAST function effectively, you can manage and manipulate data types in SQL queries with precision.