CAST Function

Converts a value from one data type to another.

Category:	Special
Alias:	When expression is the name of a table column, the operator “::”can be used in the place of the CAST keyword.
Returned data type:	The target data type.

Syntax

Syntax

CAST(expression AS data-type [(length)] )

Arguments

expression

specifies any valid SQL expression.

Data type	Expression can resolve to any FedSQL data type supported by the data source.
See	<sql-expression>
	FedSQL Expressions
	Data Type Reference

data-type

Is the target data type. See “Details” for information about supported data type conversions.

Restriction	The target data type must be supported by FedSQL and by the data source.
See	Data Types
See	Data Type Reference

length

Is an optional integer that specifies the length of the target data type. The length argument is intended for use with character values. It is important when specifying literals. When a literal value is specified, the default value is 0, and the length needs to be at least as long as the number of characters that will be generated by the CAST.

Details

FedSQL performs implicit type conversions as needed for data source support. The CAST function is provided for performing explicit type conversions.

CAST expressions are permitted anywhere expressions are permitted. In addition to converting one data type to another, CAST can be used to change the length of the target type.

The following type conversions are supported:

A character type can be converted to another character type, a numeric type, and a DATE or TIMESTAMP.
A numeric type can be converted to CHAR or another numeric type. If the target type cannot represent the non-fractional component without truncation, an exception is raised. If the target numeric cannot represent the fractional component (scale) of the source numeric, then the source is silently truncated to fit into the target. For example, casting 123.4763 as INTEGER yields 123.
A DATE or TIME value can be converted to a TIMESTAMP and a DOUBLE. TIMESTAMP can also be converted to a DOUBLE. If a DATE is converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is always 00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE component is set to the value of CURRENT_DATE at the time the CAST is executed. If a TIMESTAMP is converted to a DATE, the TIME component is silently truncated. If a TIMESTAMP is converted to a TIME, the DATE component is silently truncated.

Comparisons

The CAST function permanently modifies the data type of the specified input variable. The PUT function affects the output of the query in which it is specified.

Example

Table: CustonLine

Table: Integers

The following statements illustrate the CAST function.

Statements	Results
select cast(begintime as DATE) from custonline;	01SEP2013 02OCT2013 15OCT2013 01NOV2013 01DEC2013 02JAN2013 16JAN2013 01FEB2013 01MAR2013 15MAR2013
select si::integer as int from integers;	32767
select bi::bigint * 2::bigint as bigbang from integers;	ERROR: Numeric value out of range
select cast ('2014-04-10' as DATE);	10APR2014
select cast ('2014-04-10 10:56:49' as TIMESTAMP);	10APR2014:10:56:49
select cast ('10:56:49' as TIME);	10:56:49