Character-to-INTERVAL Conversion
Purpose
Converts a character string to an interval value.
CAST Syntax
where:
Syntax element … |
Specifies … |
character_expression |
a character expression to be cast to an INTERVAL value. |
interval_data_type |
an INTERVAL data type to which character_expression is to be converted. |
data_attribute |
one of the following optional data attributes: |
ANSI Compliance
This is ANSI SQL:2011 compliant.
As an extension to ANSI SQL, Teradata supports the specification of data attributes.
Teradata Conversion Syntax
where:
Syntax element … |
Specifies … |
character_expression |
a character expression to be cast to an INTERVAL value. |
data_attribute |
one of the following optional data attributes: |
interval_data_type |
an INTERVAL data type to which character_expression is to be converted. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Usage Notes
The character value is trimmed of leading and trailing pad characters and handled as if it was a string literal in the declaration of an INTERVAL string literal.
Character-to-INTERVAL conversion is supported for CHAR and VARCHAR types only. The source character type cannot be CLOB.
If the contents of the character string can be converted to a valid INTERVAL, then they are; otherwise, an error is returned.
You cannot convert a character data type of GRAPHIC to an INTERVAL string literal.
Example
The following query returns ' -265-11'.
SELECT CAST('-265-11' AS INTERVAL YEAR(4) TO MONTH);
Example
If the source character string contains values not normalized in the INTERVAL form, but which nevertheless can be converted to a proper INTERVAL, the conversion is made.
For example, the following query returns '-267-06'
SELECT CAST('265-30' AS INTERVAL YEAR(4) TO MONTH);
Related Topics
For details on data types and data attributes, see SQL Data Types and Literals.