Example: Finding Employee Birthdates

Teradata Vantageā„¢ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

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