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-> )
<cast-operand> := < - value-expression
>
<a>CAST-OPERAND</a>
<cast-> Â := < - data-type
>
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 |
Any numeric type means any of the following: NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE.
0: jdbc:sqlstream:> values(cast(date'2020-08-23' as varchar(25)));
+-------------+
| Â EXPR$0 Â Â |
+-------------+
| 2020-08-23 Â |
+-------------+
1 row selected
(Note that if an inadequate output specification is supplied, no rows are selected:
values(cast(date'2020-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'2020-08-23' as char(25)));
+----------------------------+
| Â Â Â Â Â EXPR$0 Â Â Â Â Â |
+----------------------------+
| 2020-08-23 Â Â Â Â Â Â Â Â |
+----------------------------+
1 row selected
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
There are two ways to convert a string to a timestamp. The first uses CAST. The other uses CHAR_TO_TIMESTAMP.
s-Server supports parsing ISO 8601 timestamps with the exception of week dates.
All of the following date strings can be validly cast to timestamp:
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
2020295T14:21:01
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
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 CHAR_TO_TIMESTAMP method.
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.
0: jdbc:sqlstream:> values(cast(' 21:23:45.0' AS TIME));
'EXPR$0'
'21:23:45'
1 row selected
See also Note A.
0: jdbc:sqlstream:> values(cast('2020-02-19' AS DATE));
'EXPR$0'
'2020-02-19'
1 row selected
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:
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.
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](/sql-reference-guide/operators/logical-operators/).
## 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