17.10 - Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

Example: Queries Using Dates

To list all male employees currently over 50 years of age, the following statement might be entered:

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

The system returns:

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

Note that 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
Three months is not a specific number of days. In this particular case, “three months” is 92 days.

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 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 ;

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.

For this statement … The system returns this value …
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