BETWEEN for DATE types in Teradata

BETWEEN operator can be used for the date types which are in different formats.

General syntax:
SELECT column FROM table WHERE date_column BETWEEN date1 AND date2


Examples:

Select name and date of birth of employees between the dates March 1 1998 and April 30 1999
Here dates are given as integers which are absolutely qualified.
SELECT name, dob
FROM employee
WHERE dob BETWEEN 980301
AND 990430
ORDER BY dob ;


Forget code requests to have a deep look in the above code.
We represented the year as 98 which is for 1998.

If we want to represent the year 1898, then ?..
Here Teradata believes that the year in YY format is in the latest century..

So, the above format is used only for the easiest reference of date.

The below code is with DATE literal which the formal syntax of date types.
SELECT name, dob
FROM employee
WHERE dob BETWEEN DATE '1998-03-01'
AND DATE '1999-03-01'
ORDER BY dob ;


Here the format is YYYY-MM-DD.

To change the date format displayed above to an alternate form, change the SELECT to:
SELECT name, dob (FORMAT '99-99-99')
FROM employee
WHERE dob BETWEEN 380307 AND DATE '1942-08-25'
ORDER BY dob ;

To change the display from date format to integer format

SELECT name, dob (INTEGER)
FROM employee
WHERE dob BETWEEN 380307 AND 420825
ORDER BY dob ;