Result Value Rules - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantage™

The following rules apply to the result value:

  • If the beginning or ending bound is NULL, or both the bounds are NULL, the result is NULL.
  • If the beginning and ending bounds are NULL or if the beginning bound is NULL and the ending bound is UNTIL_CHANGED, then the type of the period defaults to PERIOD(TIMESTAMP(0)).
  • If only the beginning bound is specified, the result ending bound is the beginning bound plus one granule of the result element type. If the result ending bound exceeds or becomes equal to the maximum allowed DATE or TIMESTAMP value for result data type of PERIOD(DATE) or PERIOD(TIMESTAMP(n) [WITH TIME ZONE]), respectively, an error is reported.
  • If an ending bound is specified as a value expression and the beginning bound and ending bound have different precisions, the result precision is the higher of the two precisions. Otherwise, the result precision is the precision of the beginning bound.
  • UNTIL_CHANGED sets the result ending element to a maximum DATE or TIMESTAMP value depending on the data type of the beginning bound. If the data type of the beginning bound is TIMESTAMP(n) WITH TIME ZONE, the result ending element is set to the maximum TIMESTAMP(n) WITH TIME ZONE value at UTC (that is, the time zone displacement for the ending bound is INTERVAL '00:00' HOUR TO MINUTE).
  • If the beginning bound or the ending bound or the beginning and ending bounds include a time zone value, and the ending bound is not UNTIL_CHANGED, the result data type is WITH TIME ZONE. If only one of the bounds includes a time zone value, the time zone field of the other is set to the current session time zone displacement. If both bounds include time zone values, the result bounds include the corresponding time zone value.
  • The result Period data type has an element type that is the same as the DateTime data type of the beginning bound except with the precision and time zone as defined previously.
  • The handling of leap seconds for Period data types with TIME and TIMESTAMP element types is as follows. If the value for the beginning or ending bound contains leap seconds, the seconds portion gets adjusted to 59.999999 with the precision truncated to the result precision. During this process, if the beginning and ending bounds are the same, an error is reported.