cast

Description

Returns the specified value, converted to another datatype. cast can change the nullability of the source expression, and uses the default format for date and time datatypes.

Syntax

cast (expression as datatype [(length | precision[, scale])])

Parameters

expression

is the value to be converted from one datatype or date format to another. It includes columns, constants, functions, any combination of constants, and functions that are connected by arithmetic or bitwise operators orsubqueries.

When Java is enabled in the database, expression can be a value to be converted to a Java-SQL class.

When unichar is used as the destination datatype, the default length of 30 Unicode values is used if no length is specified.

length

is an optional parameter used with char, nchar, unichar, univarchar, varchar, nvarchar, binary and varbinary datatypes. If you do not supply a length, Adaptive Server truncates the data to 30 characters for character types and 30 bytes for binary types. The maximum allowable length for character and binary expression is 64K.

precision

is the number of significant digits in a numeric or decimal datatype. For float datatypes, precision is the number of significant binary digits in the mantissa. If you do not supply a precision, Adaptive Server uses the default precision of 18 for numeric and decimal datatypes.

scale

is the number of digits to the right of the decimal point in a numeric, or decimal datatype. If you do not supply a scale, Adaptive Server uses the default scale of 0.

Examples

Example 1

Converts the date into a more readable datetime format:

select cast("01/03/63" as datetime)
go
 --------------------------
        Jan  3 1963 12:00AM

(1 row affected)

Example 2

Converts the total_sales column in the title database to a 12-character column:

select title, cast(total_sales as char(12))

Usage


Implicit conversion

Implicit conversion between types when the primary fields do not match may cause data truncation, the insertion of a default value, or an error message to be raised. For example, when a datetime value is converted to a date value, the time portion is truncated, leaving only the date portion. If a time value is converted to a datetime value, a default date portion of Jan 1, 1900 is added to the new datetime value. If a date value is converted to a datetime value, a default time portion of 00:00:00:000 is added to the datetime value.

Example 3

DATE -> VARCHAR, CHAR, BINARY, VARBINARY, DATETIME, SMALLDATETIME
TIME -> VARCHAR, CHAR, BINARY, VARBINARY, DATETIME, SMALLDATETIME
VARCHAR, CHAR, BINARY, VARBINARY, DATETIME, SMALLDATETIME -> DATE
VARCHAR, CHAR, BINARY, VARBINARY, DATETIME, SMALLDATETIME  -> TIME

Explicit conversion

If you attempt to explicitly convert a date to a datetime, and the value is outside the datetime range such as “Jan 1, 1000” the conversion is not allowed and an informative error message is raised.

Example 4

DATE -> UNICHAR, UNIVARCHAR
TIME -> UNICHAR, UNIVARCHAR
UNICHAR, UNIVARCHAR -> DATE
UNICHAR, UNIVARCHAR -> TIME

Conversions involving Java classes

Standards

ANSI SQL – Compliance level: ANSI compliant.

Permissions

Any user can execute cast.