15.10 - Data Type Default Formats - Teradata Database

Teradata Database SQL Data Types and Literals

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

Teradata SQL uses a set of default formats for displaying expressions and column data, and for conversions between data types.

For example, Teradata Database assigns a format to every column of a table at the time the table is created. If the CREATE TABLE statement omits the format for a column, then Teradata Database assigns a default format for the data type of the column.

You can override the default format by using the FORMAT phrase in conjunction with a CREATE TABLE, ALTER TABLE, SELECT, UPDATE, DELETE, MERGE, or INSERT statement. For details, see “FORMAT” on page 293.

You can change the default formats that Teradata SQL uses to display numeric, date, and time data types. For details, see “Changing the Default Formats” on page 284.

You can also use the FORMAT phrase with CAST or with Teradata conversion syntax to convert data from one type to another. For details, see “Data Type Conversions” in SQL Functions, Operators, Expressions, and Predicates.

The following table lists the default formats that Teradata SQL uses for each data type. For explanations of the formatting characters, see:

  • “FORMAT Phrase and Character Formats” on page 297
  • “FORMAT Phrase and NUMERIC Formats” on page 300
  • “FORMAT Phrase and DateTime Formats” on page 318
  •  

    Data Type

    Default Format

    BIGINT

    -(19)9

    BYTE[(n)]

    VARBYTE(n)

    BLOB[(n)]

  • If n is 32000, the default format is X(2n).
  • If n is > 32000, the default format is X(64000).
  • BYTEINT

    -(3)9

    CHARACTER[(n)]

    X(n)

    VARCHAR(n)

    X(n)

    LONG VARCHAR

  • If the server character set is UNICODE, GRAPHIC, or KANJISJIS, the default format is X(32000).
  • If the server character set is LATIN or KANJI1, the default format is X(64000).
  • CLOB[(n)]

  • If the server character set is LATIN and n is < 64000, the default format is X(n).
  • If the server character set is LATIN and n is 64000, the default format is X(64000).
  • If the server character set is UNICODE and n is < 32000, the default format is X(n).
  • If the server character set is UNICODE and n is 32000, the default format is X(32000).
  • DATE

    In ANSIDATE mode: YYYY-MM-DD.

    In INTEGERDATE mode: YY/MM/DD.

    DECIMAL[(n[,m])]

    NUMERIC[(n[,m])]

    --(I).9(F), where I = n-m and F = m.

    FLOAT

    REAL

    DOUBLE PRECISION

    -9.99999999999999E-999

    INTEGER

    -(10)9

    INTERVAL DAY

    -d(n), where n is the value of the optional defined precision.

    INTERVAL DAY TO HOUR

    -d(n) hh, where n is the value of the optional defined precision.

    INTERVAL DAY TO MINUTE

    -d(n) hh:mm, where n is the value of the optional defined precision.

    INTERVAL DAY TO SECOND

    -d(n) hh:mm:ss where n is value of the optional defined precision and there is no defined fractional precision.

    -d(n) hh:mm:ss.s(m) for fractional precision, where n is the value of the optional defined precision and m is the value of the optional defined fractional precision.

    INTERVAL HOUR

    -h(n), where n is the value of the optional defined precision.

    INTERVAL HOUR TO MINUTE

    -h(n):mm, where n is the value of the optional defined precision.

    INTERVAL HOUR TO SECOND

    -h(n):mm:ss, where n is the value of the optional defined precision and there is no defined fractional precision.

    -h(n):mm:ss.s(m) for fractional precision, where n is the value of the optional defined precision and m is the value of the optional defined fractional precision.

    INTERVAL MINUTE

    -m(n), where n is the value of the optional defined precision.

    INTERVAL MINUTE TO SECOND

    -m(n):ss, where n is the value of the optional defined precision and there is no defined fractional precision.

    -m(n):ss.s(m) for fractional precision, where n is the value of the optional defined precision and m is the value of the optional defined fractional precision.

    INTERVAL MONTH

    -m(n), where n is the value of the optional defined precision.

    INTERVAL SECOND

    -s(n), where n is the value of the optional defined precision and there is no defined fractional precision.

    -s(n).s(m) for fractional precision, where n is the value of the optional defined precision and m is the value of the optional defined fractional precision.

    INTERVAL YEAR

    -y(n), where n is the value of the optional defined precision.

    INTERVAL YEAR TO MONTH

    -y(n)-mm, where n is the value of the optional defined precision.

    NUMBER

    FN9, for both fixed point and floating point NUMBER.

    PERIOD(DATE)

    In ANSIDATE mode: ('YYYY-MM-DD', 'YYYY-MM-DD').

    In INTEGERDATE mode: ('YY/MM/DD', 'YY/MM/DD').

    PERIOD(TIME)

    ('HH:MI:SS.S(F)Z', 'HH:MI:SS.S(F)Z')

    Time zone information is displayed for PERIOD(TIME[(n)] WITH TIME ZONE), but not for PERIOD(TIME[(n)]).

    PERIOD(TIMESTAMP)

    ('YYYY-MM-DDBHH:MI:SS.S(F)Z', 'YYYY-MM-DDBHH:MI:SS.S(F)Z')

    Time zone information is displayed for PERIOD(TIMESTAMP[(n)] WITH TIME ZONE), but not for PERIOD(TIMESTAMP[(n)]).

    SMALLINT

    -(5)9

    TIME

    TIME WITH TIME ZONE

    HH:MI:SS.S(F)Z

    Time zone information is displayed for TIME WITH TIME ZONE, but not for TIME.

    TIMESTAMP

    TIMESTAMP WITH TIME ZONE

    YYYY-MM-DDBHH:MI:SS.S(F)Z

    Time zone information is displayed for TIMESTAMP WITH TIME ZONE, but not for TIMESTAMP.

    UDT

    Default format of the external type of the UDT, as specified by the transform that defines how to pass the UDT between the application and the server.

    For more information on transforms, see CREATE TRANSFORM in SQL Data Definition Language.

    You can change the default formats that Teradata SQL uses for the following data types:

     

    To change the data type default formats, you must create a specification for data formatting (SDF) file, and then use the tdlocaledef utility. For details, see Utilities.

    Teradata Database uses the changes when it assigns formats to columns of new tables where the CREATE TABLE statement does not explicitly specify the column format. Columns of tables that were created prior to changing the default formats continue to use the format that Teradata Database assigned to the column at the time the table was created.

    To view the settings of the preceding data type default formats for the current session, use the HELP SESSION statement. For more information, see “HELP” and “SHOW” in SQL Data Definition Language.