A DATE operand must be submitted in the proper form in order to achieve a correct comparison.
Arithmetic on DATE operands causes an error if a created value is not a valid date. Therefore, although a date value can be submitted in integer form for comparison purposes, a column that contains date data should be defined as data type DATE, not INTEGER.
If an integer is used for input to DATE (this is not recommended), the way to enter the first date of the year 2000 is 1000101.
For more information, see "Teradata Date and Time Expressions" in Teradata Vantage™ - Data Types and Literals, B035-1143.
Proper forms for submitting a DATE operand are:
- An integer in the form (year-1900)*10000 + month*100 + day. The form YYMMDD is only valid for the years 1900 - 1999. For the years 2000 - 2099, the form is 1YYMMDD.
- As a character string in the same form as the date against which the compare is being done or as the date field the assignment is being done.
- A character string that is qualified with a data type phrase defining the appropriate data conversion, and a FORMAT phrase defining the format.
- As an ANSI date literal, which is always valid for a date comparison with any date format.