CAST SQL Function

Previous Next

The CAST() SQL function converts a value from one data type to another.

The function is convertible to all RDBMS platforms. This function takes 1 argument and a data type specifier. Some data type specifiers have additional length and precision specifiers.

Syntax

CAST( expression as DATATYPE )

 

Example 1

This example converts a datetime value to a time value:

SELECT   CAST(CURRENT_DATE() AS SQL_TIME)

 

Example 2

This example converts a string value to a number value:

SELECT   CAST('3.14159265' AS SQL_NUMERIC(7, 5))

Notes

Even though this is a convertible function, supported on all RDBMS platforms, the translation of the data type specifier to the RDBMS-specific data type differs from one RDBMS to another.
The outcome of CAST may also differ.

The following tables gives an overview of the data type specifiers supported by USoft and the translation to the RDBMS specific data type.

 

USoft

Oracle

MS SQL Server

ODBC

JDBC, Derby

SQL_BIGINT

INTEGER

INTEGER

SQL_BIGINT

BIGINT

SQL_BINARY[(N)]

RAW(N)

BINARY[(N)]

SQL_BINARY[(N)]

CHAR FOR BIT DATA

SQL_CHAR[(N)]

CHAR[(N)]

CHAR[(N)]

SQL_CHAR[(N)]

CHAR[(N)]

SQL_DATE

(*1)

DATE

SQL_DATE

DATE

SQL_DECIMAL[( P [,S] ])

NUMBER[( P [,S] )]

DECIMAL[( P [,S] )]

SQL_DECIMAL[( P [,S] ])

DECIMAL[( P [,S] )]

SQL_DOUBLE

FLOAT

FLOAT

SQL_DOUBLE

DOUBLE

SQL_FLOAT

FLOAT

FLOAT

SQL_FLOAT

FLOAT

SQL_INTEGER

INTEGER

INTEGER

SQL_INTEGER

INTEGER

SQL_LONGVARBINARY

(*2)

VARBINARY(MAX)

SQL_LONGVARBINARY

BLOB

SQL_LONGVARCHAR

(*2)

VARCHAR(MAX)

SQL_LONGVARCHAR

CLOB

SQL_NCHAR[(N)]

NCHAR[(N)]

NCHAR[(N)]

SQL_NCHAR[(N)]

CHAR[(N)]

SQL_NLONGVARCHAR

(*2)

NVARCHAR(MAX)

SQL_NLONGVARCHAR

CLOB

SQL_NUMERIC[( P [,S] ])

NUMBER[( P [,S] )]

DECIMAL[( P [,S] )]

SQL_NUMERIC[( P [,S] ])

DECIMAL[( P [,S] )]

SQL_NVARCHAR[(N)]

NVARCHAR2(N)

NVARCHAR[(N)]

SQL_NVARCHAR[(N)]

VARCHAR[(N)]   (*3)

SQL_REAL

REAL

REAL

SQL_REAL

REAL

SQL_SMALLINT

SMALLINT

SMALLINT

SQL_SMALLINT

SMALLINT

SQL_TIME

(*1)

TIME

SQL_TIME

TIME

SQL_TIMESTAMP

TIMESTAMP

DATETIME

SQL_TIMESTAMP

TIMESTAMP

SQL_TINYINT

SMALLINT

SMALLINT

SQL_TINYINT

SMALLINT

SQL_VARBINARY[(N)]

RAW(N)

VARBINARY[(N)]

SQL_VARBINARY[(N)]

VARCHAR FOR BIT DATA

SQL_VARCHAR[(N)]

VARCHAR2(N)

VARCHAR[(N)]

SQL_VARCHAR[(N)]

VARCHAR[(N)]    (*3)

(*1) Oracle does not have means to get a date-part or time-part out of a date using the CAST() function. For these two data types, USoft performs the following translation:
 CAST(<date> AS SQL_DATE) à TRUNC(<date>)
 CAST(<date> AS SQL_TIME) à TO_CHAR(<date>,'HH24:MI:SS')

 

(*2) Oracle does not allow casting to a (N)CLOB or BLOB. For these three data types, USoft translates the CAST() into TO_(N)CLOB() / TO_BLOB()

 

(*3) Derby does not allow casting numbers to VARCHAR directly. An additional CAST as CHAR() is automatically performed first.

 

See also

SQL Functions