Examples: DATASET Data Type - Teradata Vantage

Example: Queries Using Dates

The following statement lists male employees over age 50:

SELECT name, dob
FROM employee
WHERE CURRENT_DATE > ADD_MONTHS (dob, 12*50)
AND gender = 'M' ;

The system returns:

Name                            DOB
---------                ----------
Russell S                1932-06-05
Carter J                 1935-03-12
Inglis C                 1938-03-07

CURRENT_DATE is used in the expression to get the current date from the system.

To project a date three months from the date of birth of employee Russell, enter:

SELECT name, ADD_MONTHS (dob,3)
FROM employee
WHERE name = ’Russell S’ ;

The system returns:

Name                 ADD_MONTHS(dob,3)
---------            -----------------
Russell S            1932-09-05

Example: Using an Integer to Represent a Date

To list employees who were born between March 7, 1938, and August 25, 1942, you can specify the date information as follows:

SELECT name, dob
FROM employee
WHERE dob BETWEEN 380307
AND DATE '1942-08-25'
ORDER BY dob ;

In this example, the first date (380307) is an integer representing yymmdd. The values of the dob column are converted to INTEGER to compare with the integer value. The second date is in the preferred DATE literal form. 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

The display of DOB is controlled by the format for Personnel.Employee.DOB: FORMAT ' MMM DD YYYY '.

Example: Changing the Date Format

To change the date format displayed in the preceding example 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 ;

This format specification changes the display to the following:

Name                    DOB
----------         --------
Inglis C           38-03-07
Peterson J         42-03-27

Example: Changing from Date Format to Integer Format

To change the display from date format to integer format, change the statement in Example 3 to:

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

This format specification changes the display to the following:

Name                       DOB
----------         -----------
Inglis C                380307
Peterson J              420327

Further examples illustrating arithmetic operations on DATE appear in the following table. Assume the system date is Jan 24, 2001.

SELECT CURRENT_DATE;

    Date
--------
01/01/24

SELECT CURRENT_DATE +3;

(Date+3)
--------
01/01/27

SELECT CURRENT_DATE -3;

(Date-3)
--------
01/01/21

SELECT CURRENT_DATE - CURRENT_DATE;

(Date-Date)
-----------
          0