CAST

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks > Functions > Scalar Functions >

CAST

Previous pageReturn to chapter overviewNext page

CAST lets you convert one value expression or data type to another value expression or data type.

Its syntax is as follows"

 
CAST ( <cast-operand> AS <cast-target> )

<cast-operand> := <value-expression>

<cast-target>  := <data-type>

 

Valid Conversions

Using CAST with source operands of the types listed in the first column below can create cast target types as listed in the second column, without restriction. Other target types are not supported.

Source Operand Types

Target Operand Types

Any numeric type (See Note A below this table.)

VARCHAR, CHAR, or any numeric type (See Note A.)

VARCHAR, CHAR

All of the above, plus, DATE, TIME, TIMESTAMP, DAY-TIME INTERVAL, BOOLEAN

DATE

DATE, VARCHAR, CHAR, TIMESTAMP

TIME

TIME, VARCHAR, CHAR, TIMESTAMP

TIMESTAMP

TIME, VARCHAR, CHAR, TIMESTAMP, DATE

DAY-TIME INTERVAL

DAY-TIME INTERVAL, BIGINT, DECIMAL, CHAR, VARCHAR

BOOLEAN

VARCHAR, CHAR, BOOLEAN

BINARY, VARBINARY

BINARY, VARBINARY

Note A: Any numeric type means any of the following: NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE.

Examples

2.1 DATE to CHAR/VARCHAR

0: jdbc:sqlstream:> values(cast(date'2008-08-23' as varchar(25)));

+-------------+

|   EXPR$0    |

+-------------+

| 2008-08-23  |

+-------------+

1 row selected

 

(Note that if an inadequate output specification is supplied, no rows are selected:

values(cast(date'2008-08-23' as varchar(9)));

'EXPR$0'

No rows selected

 

(Because the date literal requires 10 characters)

In the next case, the date is blank-padded on the right (because of the semantics of the CHAR datatype):

0: jdbc:sqlstream:> values(cast(date'2008-08-23' as char(25)));

+----------------------------+

|           EXPR$0           |

+----------------------------+

| 2008-08-23                 |

+----------------------------+

1 row selected

REAL to INTEGER

The real (NUMERIC or DECIMAL) is rounded by the cast:

0: jdbc:sqlstream:> values(cast(-1.7 as integer));

+---------+

| EXPR$0  |

+---------+

| -2      |

+---------+

1 row selected

 

STRING to TIMESTAMP

There are two ways to convert a string to a timestamp. The first uses CAST. The other uses SYS_BOOT.MGMT.CHAR_TO_TIMESTAMP.

5_2_indicator

Version 5.3 Feature

As of version 5.3, you no longer need to use the preface SYS_BOOT.MGMT.

s-Server supports parsing ISO 8601 timestamps with the exception of week dates.

Timestamps in the form year month day or year day-of-year are supported.
Timezones (n the form of +-hhmm, +-hh, +-hh:mm or Z) are optional.
Separators can in general be omitted, but if they are, day and month must both be 2 digits.
Year must always be 4 digits.
Day-of-year must always be 3 digits.
Separator between date and time should be ' ' or 'T'. It can be omitted except in the case of 7 digit dates (year day-of-year with no separator).

All of the following date strings can be validly cast to timestamp:

Timestamps using a space as a separator

2020-10-21 14:21:01

2020-10-21 00:00:00

2020-10-21 24:00:00

2020-10-21 14:21:1

2020/10/21 14:21:01

Timestamp with year-dayofyear

2020295T14:21:01

Timestamps using T as a separator

2020-10-21T14:21:01

2020-10-21T14:21:01.152

2020-10-21T14:21:01,152

2020-10-21T14:21:01,15

2020-10-21T14:21:01.152678

20201021T14:21:01

20201021T142101

20201021T142101.1522020-295T14:21:01

Timestamps with timezones

2020-10-21T14:21:01Z

2020-10-21T14:21:01+01

2020-10-21T14:21:01-01

2020-10-21T14:21:01+0130

2020-10-21T14:21:01+01:30

 

If the input string is not in the appropriate format to be CAST, then to convert the string to a timestamp, you must use the SYS_BOOT.MGMT.CHAR_TO_TIMESTAMP. method.

5_2_indicator

Version 5.3 Feature

As of version 5.3, you no longer need to use the preface SYS_BOOT.MGMT.

TIMESTAMP to STRING

0: jdbc:sqlstream:> values(cast( TIMESTAMP '2020-02-19 21:25:35' AS VARCHAR(25)));

'EXPR$0'

'2020-02-19 21:25:35'

1 row selected

 

Note that CAST requires a TIMESTAMP-literal to have literally the full format of 'yyyy-mm-dd hh:mm:ss'. If any part of that full format is missing, the literal is rejected as illegal, as seen below:

0: jdbc:sqlstream:> values( TIMESTAMP '2020-02-19 21:25');

Error: Illegal TIMESTAMP literal '2020-02-19 21:25':

                                 not in format 'yyyy-MM-dd HH:mm:ss' (state=,code=0)

0: jdbc:sqlstream:> values( TIMESTAMP '2020-02-19 21:25:00');

'EXPR$0'

'2020-02-19 21:25:00'

1 row selected

 

Also, if an inadequate output specification is supplied, no rows are selected:

values(cast( TIMESTAMP '2020-02-19 21:25:35' AS VARCHAR(18)));

'EXPR$0'

No rows selected

(Because the timestamp literal requires 19 characters)

 

These restrictions apply similarly to CASTing to TIME or DATE types: See Note A.

STRING to TIME

0: jdbc:sqlstream:> values(cast(' 21:23:45.0' AS TIME));

'EXPR$0'

'21:23:45'

1 row selected

 

See also Note A.

STRING to DATE

0: jdbc:sqlstream:> values(cast('2020-02-19' AS DATE));

'EXPR$0'

'2020-02-19'

1 row selected

Note A

Note that CAST for strings requires that the string operand for casting to TIME or DATE have the exact form required to represent a TIME or DATE, respectively.

As shown below, the cast fails if:

the string operand includes data extraneous to the targeted type, or
the INTERVAL operand ( 'day hours:minutes:seconds.milliseconds' ) does not include necessary data, or
the specified output field is too small to hold the conversion results.

0: jdbc:sqlstream:> values(cast('2020-02-19 21:23:45.0' AS TIME));

'EXPR$0'

No rows selected

 

Fails because it includes date information not allowed as a TIME.

0: jdbc:sqlstream:> values(cast('2020-02-19 21:23:45.0' AS DATE));

'EXPR$0'

No rows selected

 

Fails because it includes time information not allowed as a DATE.

0: jdbc:sqlstream:> values(cast('2020-02-19 21' AS DATE));

'EXPR$0'

No rows selected

 

Fails because it includes time information not allowed as a DATE.

0: jdbc:sqlstream:> values(cast('2009-02-28' AS DATE));

'EXPR$0'

'2009-02-28'

1 row selected

 

Succeeds because it includes a correct representation of date string.

0: jdbc:sqlstream:> values(CAST (cast('2020-02-19 21:23:45.0' AS TIMESTAMP) AS DATE));

'EXPR$0'

'2020-02-19'

1 row selected

 

Succeeds because it correctly converts string to TIMESTAMP before casting to DATE.

 

0: jdbc:sqlstream:> values(cast('21:23' AS TIME));

'EXPR$0'

No rows selected

 

Fails because it lacks time information (seconds) required for a TIME.

(Specifying fractional seconds is allowed but not required.)

 

0: jdbc:sqlstream:> values(cast('21:23:34:11' AS TIME));

'EXPR$0'

No rows selected

 

Fails because it includes incorrect representation of fractional seconds.

0: jdbc:sqlstream:> values(cast('21:23:34.11' AS TIME));

'EXPR$0'

'21:23:34'

1 row selected

 

Succeeds because it includes correct representation of fractional seconds.

0: jdbc:sqlstream:> values(cast('21:23:34' AS TIME));

'EXPR$0'

'21:23:34'

1 row selected

 

This example succeeds because it includes correct representation of seconds without fractions of a second.

INTERVAL to Exact Numerics

CAST for intervals requires that the INTERVAL operand have only one field in it, such as MINUTE, HOUR, SECOND.

If the INTERVAL operand has more than one field, such as MINUTE TO SECOND, the cast fails, as shown below:

0: jdbc:sqlstream:sdp://bento> values ( cast (INTERVAL '120' MINUTE(3) as decimal(4,2)));

+---------+

| EXPR$0  |

+---------+

+---------+

No rows selected
 

0: jdbc:sqlstream:sdp://bento> values ( cast (INTERVAL '120' MINUTE(3) as decimal(4)));

+---------+

| EXPR$0  |

+---------+

| 120     |

+---------+

1 row selected
 

0: jdbc:sqlstream:sdp://bento> values ( cast (INTERVAL '120' MINUTE(3) as decimal(3)));

+---------+

| EXPR$0  |

+---------+

| 120     |

+---------+

1 row selected
 

0: jdbc:sqlstream:sdp://bento> values ( cast (INTERVAL '120' MINUTE(3) as decimal(2)));

+---------+

| EXPR$0  |

+---------+

+---------+

No rows selected

 

0: jdbc:sqlstream:sdp://bento> values cast(interval '1.1' second(1,1) as decimal(2,1));

+---------+

| EXPR$0  |

+---------+

| 1.1     |

+---------+

1 row selected

 

0: jdbc:sqlstream:sdp://bento> values cast(interval '1.1' second(1,1) as decimal(1,1));

+---------+

| EXPR$0  |

+---------+

+---------+

No rows selected

 

For year, decimal fractions are disallowed as input and as output.

values cast(interval '1.1' year (1,1) as decimal(2,1));

Error: org.eigenbase.sql.parser.SqlParseException: Encountered "," at line 1, column 35.

Was expecting:

   ")" ... (state=,code=0)

values cast(interval '1.1' year (1) as decimal(2,1));

Error: From line 1, column 13 to line 1, column 35:

             Illegal interval literal format '1.1' for INTERVAL YEAR(1) (state=,code=0)

values cast(interval '1.' year (1) as decimal(2,1));

Error: From line 1, column 13 to line 1, column 34:

             Illegal interval literal format '1.' for INTERVAL YEAR(1) (state=,code=0)

values cast(interval '1' year (1) as decimal(2,1));

+---------+

| EXPR$0  |

+---------+

| 1.0     |

+---------+

1 row selected

 

For additional examples, see SQL Operators: Further examples.

Limitations

SQLstream s-Server does not support directly casting numeric values to interval values. This is a departure from the SQL standard. To convert a numeric to an interval, we recommend multiplying the numeric value against a specific interval value. For example, to convert the integer time_in_millis to a day-time interval:

time_in_millis * INTERVAL '0 00:00:00.001' DAY TO SECOND

 

For example:

values cast( 5000 * (INTERVAL '0 00:00:00.001' DAY TO SECOND) as varchar(11));

'EXPR$0'

'5000'

1 row selected