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:
|
Data Type |
Default Format |
|
BIGINT |
-(19)9 |
|
BYTE[(n)] VARBYTE(n) BLOB[(n)] |
|
|
BYTEINT |
-(3)9 |
|
CHARACTER[(n)] |
X(n) |
|
VARCHAR(n) |
X(n) |
|
LONG VARCHAR |
|
|
CLOB[(n)] |
|
|
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.