Proper Forms of DATE Types in Comparisons - Teradata Database

Proper Forms of DATE Types in Comparisons

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” on page 845.

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.

Examples

The following examples use a comparison operator on a value in the Employee.DOB column (defined as DATE FORMAT ' MMMbDDbYYYY ') to illustrate correct forms for a DATE operand.

Example 1

In the first example, the operand is entered as an integer.

   SELECT * 

   FROM Employee 

   WHERE DOB = 420327 ;

Example 2

In the second example, the character string is entered in a form that agrees with the format of the DOB column.

   SELECT * 

   FROM Employee 

   WHERE DOB = 'Mar 27 1942';

Example 3

In the third example, the value is entered as a character string, and so is cast with both a data type phrase (DATE) and a FORMAT phrase.

   SELECT * 

   FROM Employee

   WHERE DOB = CAST ('03/27/42' AS DATE FORMAT 'MM/DD/YY'); 

Example 4

In the fourth example, the value is entered as an ANSI date literal, which works regardless of the date format of the column.

   SELECT * 

   FROM Employee

   WHERE DOB = DATE '1942-03-27';