Your Browser is not longer supported

Please use Google Chrome, Mozilla Firefox or Microsoft Edge to view the page correctly
Loading...

{{viewport.spaceProperty.prod}}

CAST expression

The CAST expression converts a value of a data type to a value of a different data type.

cast_expression ::= CAST ({ expression | NULL } AS data_type )


expression / NULL

CAST operand. It contains the keyword NULL or an expression expression. The value of expression may not be a multiple value with a dimension > 1.


data_type

Target data type for the result of the CAST expression.
The target data type data_type cannot contain a dimension for a multiple column.


Result


The result of the CAST expression is an atomic value of the target data type data_type. Which value is returned depends, on the one hand, on the value of the CAST operand and, on the other, on its data type.

If expression returns the NULL value or if the CAST operand contains the keyword NULL, the result of the CAST expression is the NULL value.

Apart from that, the rules for the conversion of a value to a different data type described as of "CAST expression" apply.


Combinations of initial and target data types

The data type of expression, referred to here as the initial data type, can only be combined with certain target data types. The table 23 shows which initial data types you can combine with which target data types, and which combinations are impermissible



Target
data type

Target data
type

Target
data type

Target data
type

Target
data
type

Target
data
type

Target
data type



INTEGER
SMALLINT
DECIMAL
NUMERIC

REAL
DOUBLE
PRECISION
FLOAT

CHAR
VARCHAR

NCHAR
NVARCHAR

DATE

TIME(3)

TIMESTAMP(3)

Initial data

type

INTEGER
SMALLINT
DECIMAL
NUMERIC

yes

yes

yes

yes

no

no

no

Initial data
type

REAL
DOUBLE
PRECISION
FLOAT

yes

yes

yes

yes

no

no

no

Initial data
type

CHAR
VARCHAR

yes

yes

yes

no

yes

yes

yes

Initial data
type

NCHAR
NVARCHAR

yes

yes

no

yes

yes

yes

yes

Initial data

type

DATE

no

no

yes

yes

yes

no

yes

Initial data
type

TIME(3)

no

no

yes

yes

no

yes

yes

Initial data
type

TIMESTAMP(3)

no

no

yes

yes

yes

yes

yes

Table 23: Permissible and impermissible combinations of initial and target data types for the CAST expression

Rules for converting a value to a different data type

In addition to the permitted combinations of initial and target data type (see table 23), the rules described below also apply to the conversion of a value to a different data type. The description is subdivided into three groups, depending on the target data type:

  • The target data type is a data type for integers, fixed-point numbers or floating-point numbers

  • The target data type is a data type for strings of fixed or variable length

  • The target data type is a time data type.


The target data type is a data type for integers, fixed-point numbers or floating-point numbers

  • Numeric values are rounded up or down when they have too many decimal places for the target data type. If the numeric value is too high for the target data type, you receive an error message.

    Examples

    CAST (4502.9267 AS DECIMAL(6,2))

    The value 4502.9267 is rounded down to 4502.93.

    CAST (-115.05 AS DECIMAL(2,0))

    The value -115.05 is rounded down to -115. However, since the value is too high for the target data type, an error message appears.

    CAST (2450.43 AS REAL)

    The value 2450.43 is represented as the floating-point number of the value 2.45043E3.

  • It must be possible to represent alphanumeric and national values without any loss of value as a value of the assigned target data type. Leading or trailing blanks are removed.

    Examples

    CAST ('512 ' AS SMALLINT) / CAST (N'512 ' AS SMALLINT)

    The blank at the end of the string is removed. The string '512' is represented as the small integer 512.

    CAST ('sum' AS NUMERIC)

    This is an error: The string 'sum' cannot be represented as a numeric value, because numeric literals can only contain digits.

    CAST ('255' AS REAL) / CAST (N'255' AS REAL)
    The blanks at the end of the string are removed, and the string '255' is represented as the floating-point number 2.55000E2.


The target data type is a data type for strings of fixed or variable length

  • It must be possible to represent numeric values of the data type integer, fixed-point number or floating-point number without any loss as a string of fixed or variable length. In addition, it must be possible to represent values of the data type floating-point number that are not equal to 0 in the standard form, and otherwise in the form 0E 0. The following applies to all numeric values: if the length of the value is less than the fixed length of the target data type CHAR or NCHAR, blanks are added to the end of the value; if the length of the value is less than the maximum length of the target data type VARCHAR or NVARCHAR, it is retained. If the length of the value is greater than the fixed or maximum length of the target data type, you receive an error message.

    Examples

    CAST (1234 AS CHAR(5)) / CAST (1234 AS NCHAR(5))

    The value of the integer 1234 returns the alphanumeric string '1234 ' or the national string N'1234 ' respectively.'

    CAST (25.95 AS VARCHAR(5)) / CAST (25.95 AS NVARCHAR(5))

    The value of the fixed-point number 25.95 returns the alphanumeric string '25.95' or the national string N'25.95' respectively.

    CAST (45.5E2 AS CHAR(7)) / CAST (45.5E2 AS NCHAR(7))

    The value of the floating-point number 45.5E2 returns the alphanumeric string '4.55E3 ' or the national string N'4.55E3 ' respectively.

  • Blanks are added to the end of alphanumeric and national values whose length is less than the fixed length of the target data type CHAR or NCHAR. If the length of the value is less than the maximum length of the target data type VARCHAR or NVARCHAR, it is retained. If the length of the value is greater than the fixed or maximum length of the target data type, the value is truncated to the length of the target data type. If characters other than blanks are removed, you receive a warning.

    Examples

    CAST ( 'Weekend' AS VARCHAR(5)) / CAST (N'Weekend' AS NCHAR(5))

    The string 'Weekend' is too long for the data type CHAR(5) or NCHAR(5) respectively. It is truncated to the length of the string 'Weeke', and SESAM/SQL issues a warning.

    CAST ( 'Week' AS VARCHAR(15)) / CAST (N'Week' AS NVARCHAR(15))

    The result is the alphanumeric string 'Week' or the national string N'Week' respectively. The string is not padded with blanks to the maximum length of 15 characters.

  • It must be possible to represent time values as a string. If the length of the time value is less than the fixed length of the target data type CHAR or NCHAR, blanks are added at the end of the value. If the length of the time value is less than the maximum length of the target data type VARCHAR or NVARCHAR, it is retained. If it is greater than the fixed or variable length of the target data type, you receive an error message.

    Examples

    CAST (DATE'2013-08-11' AS VARCHAR(20))

    CAST (DATE'2013-08-11' AS NVARCHAR(20))

    The result is the alphanumeric string '2013-08-11' or the national string N'2013-0811' respectively.

    CAST (DATE'2013-08-11' AS VARCHAR(5))

    The time value is too long for a string with a maximum variable length of 5. The time value is not converted and an error message appears.


The target data type is a time data type.

  • It must be possible to represent alphanumeric and national values without any loss of value as a value of the assigned target data type. Leading or trailing blanks are removed.

    Examples

    CAST (' 2013-08-11' AS DATE)

    CAST (N' 2013-08-11' AS DATE)

    The leading blank of the string is removed, and the string is converted to the data type DATE.

    CAST ('2013-08-11 17:57:35:000' AS TIMESTAMP(3))

    This is an error: The string cannot be represented as a time stamp. The separator between the components seconds and fractions of a second must be a period (.) in time stamp values.

  • The following rules apply to the conversion of time values:

    • If the target data type is DATE and the initial data type TIMESTAMP, the result value contains the date (year-month-day) of the initial value.

    • If the target data type is DATE and the initial data type TIME, you receive an error message.

    • If the target data type is TIME and the initial data type TIMESTAMP, the result value contains the time (hour:minute:second) of the initial value.

    • If the target data type is TIME and the initial data type DATE, you receive an error message.

    • If the target data type is TIMESTAMP and the initial data type DATE, the result value contains the date entry (year-month-day) of the initial value and the fields hour:minute:second set to 0 for the time.

    • If the target data type is TIMESTAMP and the initial data type TIME, the result value contains the date (year-month-day) of the current date (CURRENT_DATE) and the time (hour:minute:second) of the initial value.

Examples

CAST (TIMESTAMP '2013-08-11 17:57:35.000' AS DATE)

The result value is the date '8/11/2013'.


SELECT order_text, CAST (actual AS TIMESTAMP(3)) 
   FROM orders WHERE cust_num=106
   order_text                    actual
   Customer administration       2010-04-17 00:00:00.000
   Database design customers     2010-04-10 00:00:00.000

The derived table contains the column actual with the data type TIMESTAMP. The time stamp fields for the time are set to 0.

Powered by Atlassian Confluence and Scroll Viewport.