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