15.00 - Character-to-INTERVAL Conversion - Teradata Database

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: NAMED TITLE

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: NAMED TITLE
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.