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
Arguments
Details
Comparisons
Example

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
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
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: February 23, 2017