CAST Specification

With the CAST specification it is possible to specify a data type conversion. CAST converts the value of an expression to a specified data type.

Rules

The following rules apply to CAST:

data-type can be any (cast compatible) SQL data type supported by Mimer SQL.

The table below describes cast compatibility. (An asterisk indicates that specific limitations do also apply. For example casting an integer value to a one-field interval data type is possible, but not to a two-field interval.)


	`Exact num`	`Appr. num`	`Char/ nchar`	`Date`	`Time`	`Time-stamp`	`Year-Mon`	`Day-Time`	`Bool`	`Bin`	`Lob`
`Exact num`	`Y`	`Y`	`Y`	`N`	`N`	`N`	`Y*`	`Y*`	`N`	`N`	`N`
`Approx num`	`Y`	`Y`	`Y`	`N`	`N`	`N`	`N`	`N`	`N`	`N`	`N`
`Char/nchar`	`Y`	`Y`	`Y`	`Y`	`Y`	`Y`	`Y`	`Y`	`Y`	`N`	`N`
`Date`	`N`	`N`	`Y`	`Y`	`N`	`Y`	`N`	`N`	`N`	`N`	`N`
`Time`	`N`	`N`	`Y`	`N`	`Y`	`Y`	`N`	`N`	`N`	`N`	`N`
`Timestamp`	`N`	`N`	`Y`	`Y`	`Y`	`Y`	`N`	`N`	`N`	`N`	`N`
`Year-Month`	`Y*`	`N`	`Y`	`N`	`N`	`N`	`Y`	`N`	`N`	`N`	`N`
`Day-Time`	`Y*`	`N`	`Y`	`N`	`N`	`N`	`N`	`Y`	`N`	`N`	`N`
`Boolean`	`N`	`N`	`Y`	`N`	`N`	`N`	`N`	`N`	`Y`	`N`	`N`
`Binary`	`N`	`N`	`N`	`N`	`N`	`N`	`N`	`N`	`N`	`Y`	`N`
`Lob`	`N`	`N`	`N`	`N`	`N`	`N`	`N`	`N`	`N`	`N`	`N`

When converting a numeric or character value to fixed-length character, the value of the source expression is padded with trailing spaces, if the length of the converted value is shorter than the length of the target data type.

When converting a numeric or character value to variable-length character, no trailing spaces are padded.

A character value can be converted to a character value of another character type and/or another length if the value to convert is not longer than the length of the target (for CHARACTER) or the maximum length of the target (for VARCHAR).

A character value can be converted to a fixed-length binary value of equal length.

A character value can be converted to a variable-length binary value of another length if the value to convert is not longer than the length of the target (for CHARACTER) or the maximum length of the target (for VARCHAR).

A character value can be converted to a national character value.

A national character value can be converted to a character value. If the national character value contains non-Latin1 characters, those characters are replaced with '¿' and a warning is returned.

Character values can be converted to a numeric data type if the character string consists of a valid literal representation of the target data type.

Character values can be converted to a DATETIME or INTERVAL data type provided expression conforms to the natural limits placed on date/time values by the Gregorian calendar.

When a DATE is converted to a TIMESTAMP, the HOUR, MINUTE and SECOND fields of the target are set to zero. The other fields are set to the corresponding values in the source expression.

When a TIME is converted to a TIMESTAMP, the respective values for the YEAR, MONTH and DAY fields of the target are obtained by evaluating CURRENT_DATE. The other fields are set to the corresponding values in the source expression.

When a TIMESTAMP is converted to a DATE or TIME, the fields of the target are set to the corresponding values in the source expression. Any values in the source expression for which there are no corresponding fields in the target are ignored.

When converting from a single field INTERVAL to an exact numeric value, it must be possible to represent the INTERVAL value as an exact numeric value without the loss of leading significant digits.

When converting from an exact numeric value to a single field INTERVAL, it must be possible to represent the exact numeric as an INTERVAL value without the loss of leading significant digits.

If CAST is applied on NULL, or if expression results in NULL, then CAST returns NULL.

Character values can be converted to a BOOLEAN data type provided expression contains the string TRUE or FALSE regardless of case.

When converting a boolean value to fixed-length character, the value of the source expression is padded with trailing spaces, if the length of the converted value is shorter than the length of the target data type. The boolean value TRUE is converted to the string TRUE and FALSE is converted to the string FALSE.

When converting a boolean value to variable-length character, no trailing spaces are padded.

Example

SELECT CAST(floatcol AS DECIMAL(15,3)), CAST(charcol AS VARCHAR(10)), CAST(intcol AS CHAR(15)), CAST(decimcol AS FLOAT) FROM types_tab;