Usage Notes

Teradata Vantageā„¢ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

A Period expression can be cast as a character string representation using the CAST function or the Teradata cast syntax, or when forming the output for field mode. Assume L is the maximum length of the formatted character string for the format associated with the Period expression being cast. The resulting character string contains two strings representing the beginning and ending bounds of the Period expression, each up to length L, and each enclosed in apostrophes (' '), separated by comma and a space ( , ), and then enclosed within a left parenthesis and a right parenthesis [( )]. Thus, the maximum length of the resulting character string is 2*L +8. Assume the actual length is K (which may be less than 2*L +8, for example, if the format includes the full names of months and the specific month for a bound is July) and the target type is CHARACTER(n) or VARCHAR(n):

  • If n is equal to K, the period is cast into the resulting character string of length K.
  • If n is greater than K and the target is VARCHAR(n), the period is cast into the resulting character string with length K.
  • If n is greater than K and the target is CHARACTER(n), the period is cast into the resulting character string and trailing pad characters are added to extend to length n.
  • If n less than K and the session is in ANSI mode, a truncation error is reported.
  • If n less than K and the session is in Teradata mode, a truncated string of length n is returned.

For data of Period data types with TIME and TIMESTAMP element types, the UTC value of the Period expression is adjusted to the time zone of the value or the current session time zone if the value does not have a time zone. The exception to conversion from UTC is for an ending bound of a PERIOD(TIMESTAMP(n)) value equal to the maximum value that is used to represent UNTIL_CHANGED; in this case, the value is not changed. Due to such adjustments, the ending bound may appear less than the beginning bound in the result, although in UTC the ending bound is greater than the beginning bound. This happens since the hour value for the TIME data type wraps over every 24 hours (that is, the hour value is obtained using 'module 24').