Consider a table named employee that was created with a session dateform mode of INTEGERDATE where dob is a DATE column with a format of M3BDDBY4. To list employees who were born between January 30, 1938, and March 30, 1943, you could specify the date information as follows:
SELECT name, dob FROM employee WHERE dob BETWEEN 'Jan 30 1938' AND 'Mar 30 1943' ORDER BY dob;
The result returns the date of birth information as specified for the Employee table:
Name DOB ---------- ----------- Inglis C Mar 07 1938 Peterson J Mar 27 1942
To change the date format to an alternate form, change the SELECT to:
SELECT name, dob (FORMAT 'yy-mm-dd') FROM employee WHERE dob BETWEEN 'Jan 30 1938' AND 'Mar 30 1943' ORDER BY dob ;
The format specification changes the display to the following:
Name DOB ---------- -------- Inglis C 38-03-07 Peterson J 42-03-27