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.
cast (expression as datatype [(length | precision[, scale])])
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.
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.
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.
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.
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)
Converts the total_sales column in the title database to a 12-character column:
select title, cast(total_sales as char(12))
For more information about datatype conversion, see “Datatype conversion functions”.
cast generates a domain error when the argument falls outside the range over which the function is defined. This should happen rarely.
Use null or not null to specify the nullability of a target column. You can use null or not null with select into to create a new table and change the datatype and nullability of existing columns in the source table.
You can use cast to convert an image column to binary or varbinary. You are limited to the maximum length of the binary datatypes that is determined by the maximum column size for your server’s logical page size. If you do not specify the length, the converted value has a default length of 30 characters.
You can use unichar expressions as a destination datatype, or they can be converted to another datatype. unichar expressions can be converted either explicitly between any other datatype supported by the server, or implicitly.
If you do not specify length when unichar is used as a destination type, the default length of 30 Unicode values is used. If the length of the destination type is not large enough to accommodate the given expression, an error message appears.
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.
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
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.
DATE -> UNICHAR, UNIVARCHAR TIME -> UNICHAR, UNIVARCHAR UNICHAR, UNIVARCHAR -> DATE UNICHAR, UNIVARCHAR -> TIME
When Java is enabled in the database, you can use cast to change datatypes in these ways:
Convert Java object types to SQL datatypes.
Convert SQL datatypes to Java types.
Convert any Java-SQL class installed in Adaptive Server to any other Java-SQL class installed in Adaptive Server if the compile-time datatype of the expression (the source class) is a subclass or superclass of the target class.
The result of the conversion is associated with the current database.
ANSI SQL – Compliance level: ANSI compliant.
Any user can execute cast.
