15.10 - DATE Formats - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

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