17.10 - Default Formats - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)
The following table lists the default formats that Teradata SQL uses for each data type. For explanations of the formatting characters, see:
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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.