www.mimer.com/developer

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.

Syntax for CAST:

Example:

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

Rules:

• data-type can be any SQL data type supported by Mimer SQL.
• domain-name must be a user-defined domain. The use of domains is not permitted in routines or triggers.
• 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).
• 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.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se