Teradata BETWEEN Date

The Between operator in Teradata can be used to filter the result-set for the date type columns as well. The date column can have dates in various formats.

Teradata Between DATE syntax

The general syntax for Teradata Between date is as follows.

select column1, column2,.., columnN
from DatabaseName.TableName
where date_column between date1 and date2;

Teradata Between DATE example

Consider the following employee_details table.

empno ename dob job mgr hiredate sal deptno
1 SMITH 1988-02-24 CLERK 7902 2016-06-13 800 20
2 JONES 1988-07-10 SALESMAN 7698 2018-05-18 1600 30
3 BLAKE 1990-10-25 SALESMAN 7698 2016-03-26 1250 30
4 CLARK 1991-09-23 MANAGER 7839 2015-10-31 2975 20
5 SCOTT 1992-01-12 MANAGER 7839 2016-06-11 2850 10
6 KING 1988-08-05 ANALYST 7839 2017-05-14 2450 10
7 TURNER 1988-09-17 PRESIDENT 7566 2017-05-05 3000 20

Now we will select the name and dob of the employee whose dob is between 1-Jan-1988 to 31-Dec-1988 as below.

select ename, dob
from teradatapoint.employee_details
where dob between DATE '1988-01-01'
and DATE '1988-12-31'
order by dob;

Output:

Teradata between date

In the above example, the date format is YYYY-MM-DD.

You can change the above date format to integer format.

select ename, dob (FORMAT '99-99-99')
from teradatapoint.employee_details
where dob between 32143
and DATE '1988-12-31'
order by dob;

OR,

select ename, dob (INTEGER)
from teradatapoint.employee_details
where dob between 32143 and 32508
order by dob;

Post Views: 34,958

You may also like this!

Leave a Comment