Teradata SQL uses a default set of formats for the output of TIME and TIMESTAMP expressions and column data, and for comparison and conversion of TIME and TIMESTAMP data types.
You can change or override the TIME and TIMESTAMP format settings for field mode in the following ways:
The FORMAT phrase sets the format for a specific TIME or TIMESTAMP column or value. A FORMAT phrase overrides the system format.
The TIME and TIMESTAMP format setting pertains to data in report form, as is the case in BTEQ. The format does not control internal storage representation of data or data returned in record or indicator variable mode.
For details about the FORMAT phrase, see “FORMAT Phrase and DateTime Formats” on page 318 or “FORMAT Phrase, DateTime Formats, and Japanese Character Sets” on page 326.
The following table provides the system default formats.
|
Data Type |
System Default Format |
|
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. |
Your system administrator can change the default format for TIME and TIMESTAMP data types. Changing the default TIME and TIMESTAMP formats involves changing the values of the TIME and TIMESTAMP elements in the Specification for Data Formatting (SDF) file, and using the tdlocaledef utility to convert the information into an internal form usable by Teradata Database.
Before changing the default format, consider the effect on the following.
|
Data-entry format |
Changing the default formats changes the data entry format of TIME and TIMESTAMP data for new tables, but not for existing tables. To avoid data entry problems, however, you can enter all times and timestamps as ANSI TIME and TIMESTAMP literals. |
|
Display format |
Changing the default format changes the display of TIME and TIMESTAMP values in new tables, but not in existing tables, so values for new tables and old tables display differently. Because views are based on the underlying table, this is true for views of tables as well. |
|
Character-to-TIME and character-to-TIMESTAMP comparisons and conversions |
To compare or convert strings to TIME or TIMESTAMP values, the strings must have the same format as the TIME or TIMESTAMP values. Such comparisons can fail after you change the default format, because the format changes for new tables, views of new tables, and existing macros. To avoid format errors in character-to-TIME and character-to-TIMESTAMP comparisons, however, you can specify times and timestamps as ANSI TIME and TIMESTAMP literals. |
|
Format of TIME and TIMESTAMP values in macros being executed |
Changing the default format can change the format of TIME and TIMESTAMP values in macros, which can cause the macro to fail. |
Changing the default format does not change the TIME or TIMESTAMP format for the following:
Use HELP SESSION to view the values of the system default TIME and TIMESTAMP formats.
You can use the FORMAT phrase to set the format for a specific TIME or TIMESTAMP column or value. A FORMAT phrase overrides the TIME or TIMESTAMP system format.
Setting a different TIME or TIMESTAMP format for a column with the FORMAT phrase has the same consequences as with any other method:
A FORMAT phrase does not change the export data type of TIME or TIMESTAMP values.
For details about using the FORMAT phrase, see “FORMAT Phrase and DateTime Formats” on page 318 or “FORMAT Phrase, DateTime Formats, and Japanese Character Sets” on page 326.
For details on the components that make up a TIME and TIMESTAMP format, see “Formatting Characters for Time Information” on page 320.
The operations that set the TIME and TIMESTAMP formats are best described as a hierarchy, where one setting overrides another.
At the lowest level, the system comes with a default TIME and TIMESTAMP format. Your system administrator can override the formats by creating a custom SDF that changes the default TIME and TIMESTAMP formats.
You can override the system-level TIME or TIMESTAMP format for an individual column or individual value.
The following table summarizes the hierarchy of TIME and TIMESTAMP formats.
|
For this operation … |
The format is set for … |
And field mode display format … |
|
SELECT with FORMAT phrase |
an individual column |
as defined by FORMAT.
|
|
CREATE / ALTER TABLE, with FORMAT phrase |
||
|
Default TIME or TIMESTAMP format in custom SDF |
the system |
as defined by the TIME and TIMESTAMP format in the SDF. |
The following table is a nonexhaustive list of formats that can be used to present an output time in field mode, where the data is 13:20:53.64+03:00.
|
FORMAT Phrase |
Result |
|
FORMAT 'HH:MIBT' |
01:20 PM |
|
FORMAT 'HH:MI' |
13:20 |
|
FORMAT 'HH.MI.SS' |
13.20.53 |
|
FORMAT 'HH:MI:SSBT' |
01:20:53 nachm (nachm is German for PM.) |
|
FORMAT 'HH:MI:SSDS(F)' |
13:20:53.64 |
|
FORMAT 'HH:MI:SSDS(F)Z' |
13:20:53.64+03:00 |
|
FORMAT 'HHhMImSSs' |
13h20m53s |
The following table is a nonexhaustive list of formats for presenting an output timestamp in field mode, where the data is 85/09/12 13:20:53.64+03:00.
|
FORMAT Phrase |
Result |
|
FORMAT 'MM/DD/YYBHH:MIBT' |
85/09/12 01:20 PM |
|
FORMAT 'MMMBDD,BYYBHH:MI:SS' |
Sep 12, 85 13:20:53 |
|
FORMAT 'E3,BM4BDD,BY4BHH:MI:SSDS(F)' |
Thu, September 12, 1985 13:20:53.64 |
|
FORMAT 'YYYY-MM-DDBHH:MI:SSDS(F)Z' |
1985-09-12 13:20:53.64+03:00 |