DATE Data Type Usage Notes - Teradata Vantage

Internal Representation of DATE

Internally, Analytics Database stores each DATE value as a four-byte signed integer using the following formula:

(YEAR - 1900) * 10000 + (MONTH * 100) + DAY

where the YEAR, MONTH, and DAY components, defined appropriately for the Gregorian calendar, have the following range of values.

External Representation of DATE

The external DATE format for the client depends on whether the DATEFORM option is set to INTEGERDATE or ANSIDATE mode.

Determining the application definitions and client data types is the responsibility of the application programmer.

For an overview of the operations that set the DATEFORM option, see Hierarchy of Date Formats.

Entering Dates

The preferred way to specify dates is as an ANSI date literal. A valid ANSI date literal requires no additional format validation for SQL date operations. For example, the following SQL statement works regardless of the DATE format of the referenced column:

INSERT INTO DATETAB VALUES (DATE '2001-12-20');

Specifying the date as only a string requires matching the DATE format of the referenced column. SQL requires that date string comparisons have the same format. For example, the following statement causes an error if the DATE format of the column DOB were 'YYYY-MM-DD':

SELECT Name FROM Employee WHERE DOB = 'Jan 31 1948';

See Formatting Characters for Date Information.

You can also specify a date as a number. A date as a number requires no format checking, but entering the number for years outside of the 1900’s is not obvious and error prone. See the subsequent section about numeric date validation in the subsequent section.

The following is an example of 2001-12-20 entered as a number.

INSERT INTO DATETAB VALUES (1011220);

String Date Validation

When converting a string into a date value, Vantage validates the components of the string as described in the following table.

For more information on CHARACTER to DATE conversions, see Character-to-DATE Conversion.

Numeric Date Validation

Although not recommended, you can enter dates as numbers in the database storage format. Analytics Database stores each DATE value as a four-byte integer using the following formula:

(year - 1900) * 10000 + (month * 100) + day

Allowable date values range from AD January 1, 0001 to AD December 31, 9999. For example, December 31, 1985 is stored as the integer 851231; July 4, 1776 stored as -1239296; and March 30, 2041 stored as 1410330.

The Century Break feature does not affect numeric dates.

Vantage exports dates in this numeric format if the current DATEFORM setting is set to INTEGERDATE.

The following table demonstrates how numeric dates are interpreted when inserted into a column. The third date was probably intended to be 1990-12-01.

This formula best fits two-digit dates in the 1900s. Because of the difficulty of using this format outside of the 1900s, Teradata recommends specifying dates as ANSI date literals instead.

See Numeric-to-DATE Conversion.

Implicit and Explicit DATE Conversion

Vantage performs implicit conversion on DATE types for operations such as assignment and comparison. This may include conversion from DATE to TIMESTAMP types. You can also use CAST to explicitly convert DATE to TIMESTAMP or other types.

For more information about all the cases in which implicit conversion of DATE is performed, see Implicit Conversion of DateTime Types.

Arithmetic Operations on DATE

A field that is declared as DATE can be operated on using addition and subtraction. The MIN, MAX, AVERAGE, and COUNT aggregate operators can also be used with DATE values.

You can perform arithmetic operations on DATE data. For example, you can add or subtract days from a DATE field or the DATE built-in value. The result is also a DATE data type.

The number of days between two dates can be calculated by subtracting one DATE value from another DATE value. The result is an integer.

Vantage handles month-to-month, year-to-year, and leap year arithmetic automatically, and does not store invalid dates, such as 1998-02-30.

In the following column definition, DOB is assigned the DATE data type.

DOB DATE FORMAT 'YYYY-MM-DD' NOT NULL

Calendar functions and CALENDAR System View

Vantage provides a suite of calendar functions and the SYS_CALENDAR.CALENDAR view to support DateTime operations that use calendar attributes. For example, the td_day_of_month function returns the number of days from the beginning of the month to the specified date. The calendar functions provide better performance as compared to using the CALENDAR view to get similar results.

For more information about the calendar functions, see Calendar Functions.

You can use the CALENDAR view to get attributes for any date between the years 1900 and 2100, such as which day of the week or which week of the month a date falls on.

You are encouraged to define views on the CALENDAR view because of its convenience. A useful view to define on CALENDAR is TODAY, defined as follows.

CREATE VIEW Today AS (
SELECT *
FROM SYS_CALENDAR.Calendar
WHERE SYS_CALENDAR.Calendar.calendar_date = DATE
);

CALENDAR permits easy specification of arithmetic expressions and aggregation. This is useful in OLAP environments where values aggregated by weeks, months, year-to-date, years, and so on, are common.

See System Calendar View.