15.10 - DATE Formats - Teradata Database

To simplify your work with dates, Teradata Database assumes that you want dates in a preset format. Your database administrator sets that format for the system with the DateForm setting and the default date format setting in the specification for data formatting (SDF) file.

Those settings determine the following:

Export data type of DATE values

Data entry format for DATE values

Display format of DATE values in macros being executed

Date format for string-to-DATE comparisons and conversions

Display format of DATE columns in newly created tables and views

You can change or override the DATE format settings in any of the following ways:

Change the DateForm at the system level

Set the DateForm at the user or session level

Change the system default date format in the SDF

Specify a format in a FORMAT phrase

Each of these methods involves consequences that are explained in the following topics and summarized in “Hierarchy of Date Formats” on page 287.

Before you change the DateForm setting, you need to consider the effect the change has on the following:

Export data type of DATE values.

Changing the DateForm setting changes the export data type of DATE values.

Data-entry format for dates.

Changing the DateForm setting changes the data entry format of dates for new tables, but not for existing tables. To avoid data entry problems, however, you can enter all dates as ANSI DATE literals.

Display format of DATE columns.

Changing the DateForm setting changes the display of DATE values in new tables, but not in existing tables. Therefore, DATE values display differently for new tables than for old tables. Because views are based on the underlying table, this is true for views of tables as well.

String-to-DATE comparisons and conversions.

To compare or convert strings to DATE values, the strings must have the same format as the DATE values. Such comparisons can fail after you change the DateForm setting because the DATE format for new tables, views of new tables, and existing macros changes. To avoid format errors in string-to-DATE comparisons, however, you can specify dates as ANSI DATE literals.

Format of DATE values in macros being executed.

Changing the DateForm setting can change the format of DATE values in macros, which can cause the macro to fail.

Changing the DateForm setting does not change the DATE 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

When the value of DateForm is ANSIDate, the system default DATE format is YYYY-MM-DD. When the value of DateForm is IntegerDate, the system default DATE format is YY/MM/DD.

Your system administrator can change the default format that applies to DATE data types when the DateForm is set to IntegerDate. Changing the default DATE format involves changing the value of the DATE element in a custom Specification for Data Formatting (SDF) file, and using the tdlocaledef utility to convert the information into an internal form usable by Teradata Database.

Setting or changing the default DATE format in the SDF has the same consequences as those for DateForm, except that the default DATE format in the SDF does not change the export data type of DATE values.

To view the value of the system default DATE format, use HELP SESSION.

You can use the FORMAT phrase to set the format for a specific DATE column or DATE value. A FORMAT phrase overrides the DATE format of the system, user, and session.

Setting a different DATE format for a column with the FORMAT phrase has the same consequences as with any other method:

Data entry for that column must be in the new format or the ANSI DATE literal format.

To compare or convert strings to DATE values in the column, the strings must match that format.

A FORMAT phrase does not change the export data type of DATE 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.

The following table describes the valid DATE formats. The components that make up a valid date format are the same components that make up a DATE string validation. See “String Date Validation” on page 132.

Using a format with a four-digit year, such as YYYY-MM-DD, is recommended to avoid the Year 2000 problem. To work with two-digit year formats in strings, see the Century Break feature in Utilities.

	ANSIDate	IntegerDate	User-Defined
Field Mode Display Format	YYYY-MM-DD	YY/MM/DD, or the value of the DATE element in a custom SDF	As defined by format
Allowed Input Separators	Any non-numeric character	Any non-numeric character	Any non-numeric character
Export Data Type	Character	Numeric	Depends on DateForm setting
Description	ANSI-specified date format.	Teradata legacy date format.	Custom date format for the system or a DATE column or value.
Example	1999-11-30	99/11/30	Jan 31, 2000

The operations that set the DATE format are best described as a hierarchy, where one setting overrides another.

At the lowest level, the system comes with a DateForm of IntegerDate (' YY/MM/DD '). Your system administrator can override that date format by changing the DateForm to ANSIDate (' YYYY-MM-DD ') or creating a custom SDF that changes the default DATE format (any valid date format).

You can override the system-level date format for a user, session, individual column, or individual value.

The following table summarizes the hierarchy of date formats.

For this operation …	The format is set for …	And using this session date setting …	And field mode display format …	The export data type is …
SELECT with FORMAT phrase	an individual column	user-defined display format	as defined by FORMAT	CHAR(10) if DateForm is ANSIDate; ELSE four-byte integer
CREATE / ALTER TABLE, with FORMAT phrase
SET SESSION DateForm	the session	ANSIDate	YYYY-MM-DD	CHAR(10)
CLI SessionOptions parcel DateForm		IntegerDate	YY/MM/DD, or the value of the DATE element in a custom SDF	four-byte integer
CREATE / MODIFY USER DateForm	the user	ANSIDate	YYYY-MM-DD	CHAR(10)
		IntegerDate	YY/MM/DD, or the value of the DATE element in a custom SDF	four-byte integer
System DBS Control utility DateForm	the system	ANSIDate	YYYY-MM-DD	CHAR(10)
		IntegerDate	YY/MM/DD, or the value of the DATE element in a custom SDF	four-byte integer
Default DATE format in custom SDF	the system	IntegerDate	as defined by the DATE format in the SDF	four-byte integer