Specify a data conversion.

 

Syntax

 

<cast specification> ::= CAST ( <cast operand> AS <data type> )

 

<cast operand> ::=

 

|

 

<value expression>

NULL

 

 

Usage

 

The CAST function converts data of one type to a different type, subject to the conversion rules below.

 

 

Notes

 

Numeric values can be converted to any other numeric data type. If the target type has a smaller scale than the source value, then the fractional component is truncated. An exception is raised if the conversion would lead to a loss of any leading significant digits.



 

Numeric values can be converted to any character string type.



 

Character strings can be converted to other character string types. If the string being converted has more characters than the maximum length of the target type, then the string is truncated to fit the length of the target type.



 

Character strings can be converted to any other data type, with the restriction that the contents of the character string must make sense for the target type. For example, if the target type is DATE, then the character string must mimic the pattern of a DATE literal – four digits, a hyphen, two digits, another hyphen, and two digits.



 

A DATE value can be converted to a character string or a TIMESTAMP with the time part filled in with 00:00:00.



 

A TIME value can be converted to a character string or a TIMESTAMP with the date part filled in with the current date.



 

A TIMESTAMP value can be converted to a character string, a DATE or a TIME.



 

Values of any datetime type can be converted to a numeric type. This functionality is a NexusDB extension.



 

Boolean values can be converted to a character string. The boolean value TRUE is converted to 'True' and FALSE is converted to 'False'.



 

The expression CAST ( NULL AS data-type ) is used to associate a data type with NULL.



 

 

Examples

 

1)

The following example casts a character string value to DOUBLE PRECISION:



 


CAST( '2.75' AS DOUBLE PRECISION )



 

2)

The following example casts an integer value to FLOAT:



 


CAST( 50 AS FLOAT )



 

3)

The following example casts a character string value to DATE:



 


CAST( '2004-10-15' AS DATE )



 

4)

The following example casts the current datetime value to VARCHAR:



 


CAST( CURRENT_TIMESTAMP AS VARCHAR(19) )



 

5)

The following example casts the null value to DECIMAL:



 


CAST ( NULL AS DECIMAL )



 

 


 

Conformance

 

SQL:2003 standard

 

NexusDB extensions

-

 

-

Core SQL

 

Casting datetime values to a numeric type