A data type that has two DateTime elements associated with it.
| The DateTime element... | Specifies... |
|---|---|
| beginning | the beginning bound of a period. |
| ending | the ending bound of a period. |
The beginning bound is inclusive, and the ending bound is exclusive; that is, the DateTime range starts at the beginning bound and extends up to but not including the ending bound.
Syntax
PERIOD(DATE) [ attribute [...] ]
- attributes
- Appropriate data type, column storage, or column constraint attributes. See Core Data Type Attributes and Storage and Constraint Attributes for specific information.
ANSI Compliance
Period types are a Teradata extension to the ANSI SQL:2011 standard.
Storage
A PERIOD(DATE) field is a fixed length data type and is saved as two DATE values.
| Element Type | Field Size in bytes | Maximum Size in bytes in the row |
|---|---|---|
| DATE | 8 | 8 |
External Representation of PERIOD(DATE)
In field mode, Teradata Database returns PERIOD(DATE) data as character data.
Assume L is the maximum length of the formatted character string for the format associated with this Period data type. The resulting character string contains two strings representing the beginning and ending bounds of the period value expression, each up to length L , and each enclosed in apostrophes ('), separated by comma and a space (,), and then enclosed within a left and 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.
For modes other than field mode, and for input data, the external representation of PERIOD(DATE) consists of two consecutive date values. Each date value is a 4-byte, signed integer flipped to client form. This integer represents a date in the same manner as for a DATE data type, for example, (10000*(year-1900)) + (100*month) + day.
Restrictions
A primary index column or partitioning column cannot be a column that has a Period data type.
Example: PERIOD(DATE) Data Type
The following CREATE TABLE statement defines a PERIOD(DATE) column with a default value set using a Period literal.
CREATE TABLE t1 ( employee_id INTEGER, employee_name CHARACTER(15), employee_duration PERIOD(DATE) DEFAULT PERIOD '(2005-02-03, 2006-02-03)' );
Example: Period Parameter in a Java UDF
REPLACE FUNCTION PDT_UDF (P1 PERIOD(DATE), P2 PERIOD(DATE)) RETURNS PERIOD (DATE) LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.pdt_udf'; public static java.sql.Struct pdt_udf(java.sql.Struct p1, java.sql.Struct p2) throws SQLException
Alternatively, you can define the function as follows:
REPLACE FUNCTION PDT_UDF (P1 PERIOD(DATE), P2 PERIOD(DATE)) RETURNS PERIOD (DATE) LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.pdt_udf(java.sql.Struct, java.sql.Struct) returns java.sql.Struct'; public static java.sql.Struct pdt_udf(java.sql.Struct p1, java.sql.Struct p2) throws SQLException
Example: Period Parameter in a Java External Stored Procedure
REPLACE PROCEDURE PDT_XSP(IN P1 PERIOD(DATE), INOUT P2 PERIOD(DATE), OUT P3 PERIOD(DATE)) LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.pdt_xsp'; public static void pdt_xsp(java.sql.Struct p1, java.sql.Struct[] p2, java.sql.Struct[] p3) throws SQLException
Related Topics
For information on functions and operators that apply to Period types, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.