System Default TIME & TIMESTAMP Format | Teradata Vantage - System Default TIME and TIMESTAMP Format - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

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 Vantage.

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:
  • Tables created prior to the format change
  • Tables created by users currently logged on
  • Macros executed by users currently logged on
  • Views based on tables that were created prior to the change

Use HELP SESSION to view the values of the system default TIME and TIMESTAMP formats.