15.10 - PERIOD(DATE) Data Type - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

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.

where:

 

Syntax element …

Specifies …

attributes

appropriate data type, column storage, or column constraint attributes. See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information.

The following data type attributes are supported for a PERIOD(DATE) column:

  • NULL and NOT NULL
  • FORMAT 'format string'
  • TITLE
  • NAMED
  • DEFAULT NULL
  • DEFAULT value
  • WITH DEFAULT
  • For details on these data type attributes, see Chapter 11: “Default Value Control Phrases” and Chapter 12: “Data Type Formats and Format Phrases.”

     

    The following data type attributes are not supported for a PERIOD(DATE) column:

  • DEFAULT USER
  • DEFAULT DATE
  • DEFAULT TIME
  • DEFAULT CURRENT_DATE
  • DEFAULT CURRENT_TIME[(n)]
  • DEFAULT CURRENT_TIMESTAMP[(n)]
  • UPPERCASE or UC
  • CASESPECIFIC or CS
  • CHARACTER SET
  • Period types are a Teradata extension to the ANSI SQL:2011 standard.

    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

    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.

    UDFs or external stored procedures that are written in Java do not support arguments or return values that have a Period data type.

    A primary index column or partitioning column cannot be a column that has a Period 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)'
    );

    For information on functions and operators that apply to Period types, see SQL Functions, Operators, Expressions, and Predicates.