15.00 - Period-to-TIME Conversion - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

Period-to-TIME Conversion

Purpose

Converts Period data to a TIME value.

CAST Syntax

where:

 

Syntax element …

Specifies …

period_expression

the Period data expression to be converted.

fractional_seconds_precision

a single digit representing the number of significant digits in the fractional portion of the SECOND field.

Values for fractional_seconds_precision range from 0 through 6 inclusive.

The default precision is 6.

time_data_attribute

one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • ANSI Compliance

    This is ANSI SQL:2011 compliant.

    As an extension to ANSI, CAST permits the use of TIME data attribute phrases.

    Usage Notes

    A PERIOD(TIME(n) [WITH TIME ZONE]) or PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) value can be cast as TIME[(n)] [WITH TIME ZONE] using the CAST function. The source last value must be equal to the source beginning bound; otherwise, an error is reported.

    If the target precision is higher than the source precision, trailing zeros are added in the result to adjust the precision. If the target precision is lower than the source precision, an error is reported.

    If the source type is PERIOD(TIME(n) [WITH TIME ZONE]) or PERIOD(TIMESTAMP(n) [WITH TIME ZONE]), the result time portion is obtained from time portion of the source beginning bound. If both the source and target type are WITH TIME ZONE, the result time zone field is set to the time zone displacement of the source beginning bound. If only the target type is WITH TIME ZONE, the result time zone field is set to the current session time zone displacement.

    If the source type is PERIOD(DATE), an error is reported.

    Example  

    Assume pt is a PERIOD(TIME(2)) column in table t with a value of PERIOD '(12:12:12.34, 12:12:12.35)'.

    In the following example, a PERIOD(TIME(2)) column is cast as TIME(6). The TIME(6) result is obtained from the source beginning element with trailing zeros added to the fractional seconds to make the precision 6.

       SELECT CAST(pt AS TIME(6)) FROM t;

    The following is returned:

       12:12:12.340000

    Related Topics

    For details on data types and data attributes, see SQL Data Types and Literals.