CAST
CAST lets you convert one value expression or data type to another value expression or data type.
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 (NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE) |
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 |
Examples
2.1 DATE to CHAR/VARCHAR
+-------------+ | 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):
+----------------------------+ | EXPR$0 | +----------------------------+ | 2008-08-23 | +----------------------------+ 1 row selected
REAL to INTEGER
The real (NUMERIC or DECIMAL) is rounded by the cast:
+---------+ | EXPR$0 | +---------+ | -2 | +---------+ 1 row selected
STRING to TIMESTAMP
There are two ways to convert a string to a timestamp. The first uses CAST, as shown in the next topic. The other uses Char To Timestamp(Sys).
Using CAST to Convert a String to a Timestamp
The example below illustrates this method for conversion:
'EXPR$0' '2007-02-19 21:23:45' 1 row selected
If the input string lacks any one of the six fields (year, month, day, hours, minutes, seconds), or uses any delimiters different from those shown above, CAST will not return a value. (Fractional seconds are disallowed.)
If the input string is thus not in the appropriate format to be CAST, then to convert the string to a timestamp, you must use the CHAR_TO_TIMESTAMP method.
Using CHAR_TO_TIMESTAMP to convert a String to a Timestamp
When the input string is not in the appropriate format to be CAST, you can use the CHAR_TO_TIMESTAMP method. It has the additional advantage that you can specify which parts of the timestamp string you wish to use in subsequent processing, and create a TIMESTAMP value containing only those. To do so, you specify a template that identifies which parts you want, such as 'yyyy-MM' to use only the year and month parts.
The input-date-time string-to-be-converted can contain all or any parts of a full
timestamp, that is, values for any or all of the standard elements ('yyyy-MM-dd hh:mm:ss'). If
all these elements are present in your input string, and 'yyyy-MM-dd hh:mm:ss' is the template
you supply, then the input-string elements are interpreted in that order as year, month, day,
hour, minute, and seconds, such as in '2009-09-16 03:15:24'. The yyyy cannot be uppercase; the
hh can be uppercase to mean using a 24-hour clock. For many examples of valid specifiers, see
the table and examples later in this topic. For the full range of valid specifiers, see Class SimpleDateFormat
CHAR_TO_TIMESTAMP uses the template you specify as a parameter in the function call. The
template causes the TIMESTAMP result to use only the parts of the input-date-time value that
you specified in the template. Those fields in the resulting TIMESTAMP will then contain the
corresponding data taken from your input-date-time string; fields not specified in your
template will use default values (see below). The format of the template used by
CHAR_TO_TIMESTAMP is defined by Class SimpleDateFormat
The function-call syntax is as follows:
CHAR_TO_TIMESTAMP('<format_string>','<input_date_time_string>')
Where <format_ string> is the template you specify for the parts of <date_time_string> you want, and <input_date_time_string> is the original string that is being converted to a TIMESTAMP result.
Each string must be enclosed in single quotes, and each element of the <input_date_time_string> must be in the range for its corresponding element in the template. Otherwise, no result is returned.
Example 1
-
The input-string-element whose position corresponds with MM must be an integer from 1 to 12, because anything else does not represent a valid month.
-
The input-string-element whose position corresponds with dd must be an integer from 1 to 31, because anything else does not represent a valid day.
-
However, if MM is 2, dd cannot be 30 or 31, because February never has such days.
However, for months or days, the default starting value substituted for the omitted parts is 01.
For example, using '2009-09-16 03:15:24' as your input string, you can obtain a TIMESTAMP containing only the date, with zeros for the other fields such as hours, minutes, or seconds, by specifying
CHAR_TO_TIMESTAMP('yyyy-MM-dd','2009-09-16 03:15:24').
The result would be the TIMESTAMP 2009-09-16 00:00:00.
Example 2
-
If the call had kept hours and minutes in the template while omitting months, days, and seconds, as illustrated in the following call --- --- CHAR_TO_TIMESTAMP('yyyy-hh-mm','2009-09-16 03:15:24') --- --- then the resulting TIMESTAMP would be 2009-01-01 03:15:00.
| Template | Input String | Output TIMESTAMP | Notes |
|---|---|---|---|
'yyyy-MM-dd hh:mm:ss'
|
'2009-09-16 03:15:24'
|
'2009-09-16 03:15:24'
|
Input string MUST use the form 'yyyy-MM-dd hh:mm:ss' or a subset or reordering thereof; using an input string like 'Wednesday, 16 September 2009 03:15:24' will NOT work, meaning that no output will result. |
'yyyy-mm'
|
'2012-02-08 07:23:19'
|
'2012-01-01 00:02:00'
|
The template above specifies only year first and minutes second, so the second element in the input string ("02") is used as minutes. Default values are used for Month and Day ("01") and for hours and seconds ("00"). |
'yyyy-ss-mm'
|
'2012-02-08 07:23:19'
|
'2012-01-01 00:08:02'
|
The template above specifies only year, seconds, and minutes, in that order, so the second element in the input string ("02") is used as seconds and the third as minutes ("08"). Default values are used for Month and Day ("01") and for hours ("00"). |
'MMM dd, yyyy'
|
'March 7, 2010'
|
'2010-03-07 00:00:00'
|
MMM in the template above matches "March"; the template's 'comma space' matches the input string. --- --- If the template lacks the comma, so must the input string, or there is no output; --- --- If the input string lacks the comma, so must the template. |
'MMM dd,'
|
'March 7, 2010'
|
'1970-03-07 00:00:00'
|
Note that the template above doesn't use a year specifier, causing the output TIMESTAMP to use the earliest year in this epoch, 1970. |
'MMM dd,y'
|
'March 7, 2010'
|
'2010-03-07 00:00:00'
|
Using the template above, if the input string were 'March 7, 10', the output TIMESTAMP would be '0010-03-07 00:00:00'. |
'M-d'
|
'2-8'
|
'1970-02-08 00:00:00'
|
Absent a yyyy specifier in the template, as above, the earliest year in this epoch (1970) is used. An input string of '2-8-2012' would give the same result; using '2012-2-8' would give no result because 2012 is not a valid month. |
'MM-dd-yyyy'
|
'06-23-2012 10:11:12'
|
'2012-06-23 00:00:00'
|
Dashes as delimiters (as above) are fine, if template and input both use them in the same positions. Since the template omits hours, minutes, and seconds, zeroes are used in the output TIMESTAMP. |
'dd-MM-yy hh:mm:ss'
|
'23-06-11 10:11:12'
|
'2011-06-23 10:11:12'
|
You can have the specifiers in any order as long as that order matches the meaning of the input string you supply, as above. The template and input string of the next example below have the same meaning (and the same output TIMESTAMP) as this example, but they specify months before days and seconds before hours. |
'MM-dd-yy ss:hh:mm'
|
'06-23-11 12:10:11'
|
'2011-06-23 10:11:12'
|
In the template used above, the order of the month and day specifiers is reversed from the example just above, and the specifier for seconds is before hours instead of after minutes; but because the input string also puts months before days and seconds before hours, the meaning (and the output TIMESTAMP) is the same as the example ABOVE. |
'yy-dd-MM ss:hh:mm'
|
'06-23-11 12:10:11'
|
'2006-11-23 10:11:12'
|
The template used above reverses (compared to the prior example above) the years and months specifiers, while the input string remains the same. In this case, the output TIMESTAMP uses the first element of the input string as the years, the second as the days, and the third as the months. |
'dd-MM-yy hh:mm'
|
'23-06-11 10:11:12'
|
'2011-06-23 10:11:00'
|
With seconds omitted in the template, as above, the output TIMESTAMP uses 00 seconds. Any number of y specifiers produces the same result; but if the input string inadvertently uses a 1 instead of 11 for the year, as in '23-06-1 10:11:12', then the output TIMESTAMP becomes '0001-06-23 10:11:00'. |
'MM/dd/yy hh:mm:ss'
|
|
|
Slashes as delimiters are fine, if template and input both use them in the same positions, as above; otherwise, no output. Using specifier hh, input times of 12:11:10 and 00:11:10 have the same meaning as a time in the morning. |
'MM/dd/yy HH:mm:ss'
|
'12/19/11 12:59:59' '12/19/11 21:08:07'
|
'2011-12-19 12:59:59' '2011-12-19 21:08:07'
|
The input-string values '2011-12-19 00:11:12' or '2011-12-19 12:11:12' would fail with this template because '2011' is not a month, as required/expected by the template-string 'MM/dd/yy HH:mm:ss'. However, changing the template gives useful output:
Note that for times after 12 noon, that is, for afternoon and evening times, the hours specifier must be HH instead of hh, and the input string must specify the afternoon or evening hour in 24-hour clock time, hours running from 00 to 23. --- --- Using specifier HH, input times of 12:11:10 and 00:11:10 have different meanings, the first as a time in the afternoon and the second as a time in the morning. --- --- Using the specifier hh, the times from 12:00 through 11:59:59 are morning times: --- --- Given the specifiers hh:mm:ss, the output TIMESTAMP will include '00:09:08' in the morning for both input string '12:09:08' and input string '00:09:08'; --- --- whereas --- --- Given the specifiers HH:mm:ss, the output TIMESTAMP for input string '00:09:08' in the morning will include '00:09:08' --- --- and the output TIMESTAMP for input string '12:09:08' in the afternoon will include '12:09:08'. |
Further examples
The examples below illustrate using various templates with CHAR_TO_TIMESTAMP, including some common misunderstandings.
values (CHAR_TO_TIMESTAMP('yyyy-hh-mm','2009-09-16 03:15:24')); 'EXPR$0' '2009-01-01 09:16:00' 1 row selected
Note that the fields in the input string above were used in the order given by the specifiers in the template, as defined by the dashes-as-delimiters in both template and input string: years first, then hours, then minutes. Since the specifiers for months and days are not present in the template, their values in the input string were ignored, with 01 substituted for both values in the output TIMESTAMP. The template specified hours and minutes as the second and third input values, so 09 became the hours and 16 became the minutes. No specifier was present for seconds, so 00 was used.
The years specifier can be alone or, after a delimiter matching the input string shows the end of the years specifier, with one of the hours:minutes:seconds specifiers:
values (CHAR_TO_TIMESTAMP('yyyy','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 00:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy hh','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected
The template above fails because it has a space-as-delimiter before the "hh" rather than the dash delimiter used in the input string's date specification;
whereas the four templates below work because they use the same delimiter to separate the years specifier from the next specifier as is used in the input string's date specification (dash in the first case, space in the second, slash in the third, and dash in the fourth).
values (CHAR_TO_TIMESTAMP('yyyy-hh','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy hh','2009 09 16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy/hh','2009/09/16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy-mm','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 00:09:00' 1 row selected
However, if the template specifies months (MM), it cannot then specify hours, minutes, or seconds unless days are also specified:
Template specifying years and months only, thus omitting days/hours/minutes/seconds from the resulting TIMESTAMP:
values (CHAR_TO_TIMESTAMP('yyyy-MM','2009-09-16 03:15:24') ); 'EXPR$0' '2009-09-01 00:00:00' 1 row selected
The next two templates fail, lacking a 'days' specifier:
values (CHAR_TO_TIMESTAMP('yyyy-MM hh','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected values (CHAR_TO_TIMESTAMP('yyyy-MM hh:','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected
The next three succeed, using a 'days' specifier:
values (CHAR_TO_TIMESTAMP('yyyy-MM-dd hh','2009-09-16 03:15:24') ); 'EXPR$0' '2009-09-16 03:00:00' 1 row selected
The template above, 'yyyy-MM-dd hh', specifies only hours (hh) without minutes or seconds. Since hh is the 4th token/element of the template, its value is to be taken from the 4th token/element of the input string '2009-09-16 03:15:24' ; and that 4th element is 03, then used as the value output for hours. Since neither mm or ss is specified, the default or initial values defined as the starting point for mm and ss are used, which are zeroes.
values (CHAR_TO_TIMESTAMP('yyyy-MM-dd ss','2009-09-16 03:15:24') ); 'EXPR$0' '2009-09-16 00:00:03' 1 row selected
The template above, 'yyyy-MM-dd ss', specifies that the 4th token/element of the input string is to be used as seconds (ss). The 4th element of the input string '2009-09-16 03:15:24' is 03, which becomes the value output for seconds as specified in the template; and since neither hh nor mm is specified in the template, their default or initial values are used, which are zeroes.
values (CHAR_TO_TIMESTAMP('yyyy-MM-dd mm','2009-09-16 03:15:24') ); 'EXPR$0' '2009-09-16 00:03:00' 1 row selected
The template above, 'yyyy-MM-dd mm', specifies that the 4th token/element of the input string is to be used as minutes (mm). The 4th element of the input string '2009-09-16 03:15:24' is 03, which becomes the value output for minutes as specified in the template; and since neither hh nor ss is specified in the template, their default or initial values are used, which are zeroes.
Further failures, lacking a 'days' specifier:
values (CHAR_TO_TIMESTAMP('yyyy-MM- mm','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected values (CHAR_TO_TIMESTAMP('yyyy-MM mm','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected values (CHAR_TO_TIMESTAMP('yyyy-MM hh','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected
About Delimiters and Values
Delimiters in the template must match those in the input string; values in the input string must be acceptable for the template specifiers to which they correspond.
As a general convention, a colon is used to separate hours from minutes, and minutes from seconds. Similarly, the general convention is to use a dash or slash to separate years from months and months from days. Any parallel usage seems to work, and the examples that follow illustrate this.
values (CHAR_TO_TIMESTAMP('MM/dd/yy hh:mm:ss','2009/09/16 03:15:24') ); 'EXPR$0' No rows selected
The example above fails because 2009 is not an acceptable value for months, which is the first specifier (MM) in the template.
values (CHAR_TO_TIMESTAMP('MM/dd/yy hh:mm:ss','09/16/11 03:15:24') ); 'EXPR$0' '2011-09-16 03:15:24' 1 row selected
The example above succeeds because the delimiters are parallel (slashes to slashes, colons to colons) and each value is acceptable for the corresponding specifier.
values (CHAR_TO_TIMESTAMP('MM/dd/yy hh/mm/ss','09/16/11 03/15/24') ); 'EXPR$0' '2011-09-16 03:15:24' 1 row selected
The example above succeeds because the delimiters are parallel (all slashes) and each value is acceptable for the corresponding specifier.
values (CHAR_TO_TIMESTAMP('MM/dd/yy hh-mm-ss','09/16/11 03-15-24') ); 'EXPR$0' '2011-09-16 03:15:24' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy|MM|dd hh|mm|ss','2009|09|16 03|15|24') ); 'EXPR$0' '2009-09-16 03:15:24' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy@MM@dd hh@mm@ss','2009@09@16 03@15@24') ); 'EXPR$0' '2009-09-16 03:15:24' 1 row selected
The examples above succeed because the delimiters are parallel and the values are acceptable per specifier.
In the following examples, note that omissions in the supplied string can cause the template value 'yyyy' to produce logical but unintended or unexpected results. The value given as the year in the resulting TIMESTAMP value derives directly from the first element in the supplied string.
VALUES(CHAR_TO_TIMESTAMP('yyyy','09-16 03:15')); 'EXPR$0' '0009-01-01 00:00:00' 1 row selected VALUES(CHAR_TO_TIMESTAMP('yyyy','16 03:15')); 'EXPR$0' '0016-01-01 00:00:00' 1 row selected
TIMESTAMP to STRING
values(cast( TIMESTAMP '2007-02-19 21:25:35' AS VARCHAR(25))); 'EXPR$0' '2007-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:
values( TIMESTAMP '2007-02-19 21:25'); Error: Illegal TIMESTAMP literal '2007-02-19 21:25': not in format 'yyyy-MM-dd HH:mm:ss' (state=,code=0) values( TIMESTAMP '2007-02-19 21:25:00'); 'EXPR$0' '2007-02-19 21:25:00' 1 row selected
Also, if an inadequate output specification is supplied, no rows are selected:
values(cast( TIMESTAMP '2007-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.
STRING to TIME
values(cast(' 21:23:45.0' AS TIME)); 'EXPR$0' '21:23:45' 1 row selected
For more information, see Note A.
STRING to DATE
values(cast('2007-02-19' AS DATE)); 'EXPR$0' '2007-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.
values(cast('2007-02-19 21:23:45.0' AS TIME)); 'EXPR$0' No rows selected
Fails because it includes date information not allowed as a TIME.
values(cast('2007-02-19 21:23:45.0' AS DATE)); 'EXPR$0' No rows selected
Fails because it includes time information not allowed as a DATE.
values(cast('2007-02-19 21' AS DATE)); 'EXPR$0' No rows selected
Fails because it includes time information not allowed as a DATE.
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.
values(CAST (cast('2007-02-19 21:23:45.0' AS TIMESTAMP) AS DATE)); 'EXPR$0' '2007-02-19' 1 row selected
Succeeds because it correctly converts string to TIMESTAMP before casting to DATE.
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.)
values(cast('21:23:34:11' AS TIME)); 'EXPR$0' No rows selected
Fails because it includes incorrect representation of fractional seconds.
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.
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:
values ( cast (INTERVAL '120' MINUTE(3) as decimal(4,2))); +---------+ | EXPR$0 | +---------+ +---------+ No rows selected values ( cast (INTERVAL '120' MINUTE(3) as decimal(4))); +---------+ | EXPR$0 | +---------+ | 120 | +---------+ 1 row selected values ( cast (INTERVAL '120' MINUTE(3) as decimal(3))); +---------+ | EXPR$0 | +---------+ | 120 | +---------+ 1 row selected values ( cast (INTERVAL '120' MINUTE(3) as decimal(2))); +---------+ | EXPR$0 | +---------+ +---------+ No rows selected values cast(interval '1.1' second(1,1) as decimal(2,1)); +---------+ | EXPR$0 | +---------+ | 1.1 | +---------+ 1 row selected 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
Amazon Kinesis Data Analytics does not support directly casting numeric values to interval values. This is a departure from the SQL:2008 standard. The recommended way to convert a numeric to an interval is to multiply 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