16.20 - Examples - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Advanced SQL Engine
Teradata Database
March 2019
Programming Reference

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: Entering the Operand as an Integer

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

   SELECT * 
   FROM Employee 
   WHERE DOB = 420327 ;

Example: Entering the Character String to Agree with the DOB column Format

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: Entering the Character String with Data Type and FORMAT Phrases

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: Entering the Value as an ANSI Date Literal

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';