Period-to-TIMESTAMP Conversion - Teradata Database

Period-to-TIMESTAMP Conversion

Purpose

Converts Period data to a TIMESTAMP 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.
timestamp_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 the FORMAT phrase to enable alternative output formatting of DateTime data.

Usage Notes

A PERIOD(DATE), PERIOD(TIME(n) [WITH TIME ZONE]), or PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) value can be cast as TIMESTAMP[(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 source type is PERIOD(TIME(n) [WITH TIME ZONE]) or PERIOD(TIMESTAMP(n) [WITH TIME ZONE]):

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(DATE), the result is formed from the source beginning bound and a time portion of 0 adjusted with respect to the current session time zone, and, if the target type is WITH TIME ZONE, the current session time zone displacement.

If the source type is PERIOD(TIME(n) [WITH TIME ZONE]), the source beginning bound (in UTC) is adjusted with respect to the current session time zone displacement. The timestamp portion of the result is formed from CURRENT_DATE and the time portion of the source beginning bound obtained after the above adjustment. The resulting timestamp value is converted to UTC. 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(TIMESTAMP(n) [WITH TIME ZONE]), the result timestamp portion is the timestamp 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.

Example  

Assume pts is a PERIOD(TIMESTAMP(2)) column in table t with a value of PERIOD '(2005‑02-03 12:12:12.34, 2005-02-03 12:12:12.35)'.

In the following example, column pts is cast as TIMESTAMP(6). The result is the source beginning bound with trailing zeros added to the fractional seconds to make the precision 6.

   SELECT CAST(pts AS TIMESTAMP(6)) FROM t;

The following is returned:

   2005-02-03 12:12:12.340000

Related Topics

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