Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 33 34 35 36 37 38 < 39 > 40 41 42 43 44 45 .. 241 >> Next


The default display format for DATE is YY/MM/DD.

Default DATE Display 5

You can override he default display with a FORMAT phrase. Note that if you use a FORMAT phrase, the DATE values must be defined in the specified format for both insertion and retrieval. For example, the Employee table defines the DOB column as follows:

DOB DATE FORMAT 'MMMbDDbYYYY' NOT NULL,

Note: All newly written applications should use a date format such as ‘YYYY-MM-DD’ to avoid the so-called Year 2000 problem.

Teradata RDBMS for UNIX SQL Reference

5-13
Data Definition

DATE Numeric Data Type

Inserting DATE

Retrieving DATE

Arithmetic Operations on DATE

To insert a birthdate of October 17, 1948, the value should be defined as:

INSERT INTO employee

(name, empno, deptno, dob, sex, edlev)

VALUES

('Orebo B', 10005, 300, 'Nov 17 1957', 'M', 18) ;

Note: The form of the character string constant used in the INSERT is correct for the format defined for DOB, ‘MMM DD YYYY’.

To retrieve the same date, define the WHERE condition should be defined as:

SELECT Name FROM Employee WHERE DOB = 'Oct 17 1948';

A field that is declared as DATE can be operated on using addition and subtraction. The MIN, MAX, AVERAGE, and COUNT aggregate operators can also be used with DATE values.

You can perform arithmetic operations on DATE data. For example, a number of days can be added to or subtracted from a DATE field, or from the DATE built-in value. The result of such operations is a value that is also a DATE data type.

The number of days between two dates can be calculated by subtracting one DATE value from another DATE value.The result is an integer.

The Teradata RDBMS handles month-to-month, year-to-year, and leap year arithmetic automatically; it does not store invalid dates, such as 84/02/30.

In the following column definition, DOB is assigned the DATE data type (a 4-digit year form is recommended):

DOB DATE FORMAT 'YYYY/MM/DD' NOT NULL,

The following operations can be performed on data defined as DATE:

• Arithmetic (addition and subtraction)

• ADD_MONTHS function

• Comparison (=, <>, >, >=, <, <=)

• Conversion (format)

• EXTRACT

5-14

Teradata RDBMS for UNIX SQL Reference
Data Definition

DATE Numeric Data Type

Example 1

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

SELECT name, dob FROM employee

WHERE DATE > ADD_MONTHS (dob, 12*50)

AND sex = 'M' ;

The system returns:

Name______ ____

DOB

Russell S Carter J Inglis C

1932/06/05

1935/03/12

1938/03/07

Note that DATE is used in the expression to get the current date from the system.

To project a date 3 months from Russell’s date of birth, enter:

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

The system returns:

Name______

Russell S

ADD_MONTHS(dob.3)

1932/09/05

Note: Three months is not a specific number of days. In this case it is 92.

Example 2

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

SELECT name, dob FROM employee WHERE dob BETWEEN 380307 AND '082542'(DATE FORMAT 'mmddyy') ORDER BY dob ;

In this example, the first date string is integer, in the form yymmdd, and requires no qualification. The second string is in the form mmddyy and must be identified to the system. The result returns the date of birth information as specified for the Employee table:

Name

Inglis C Peterson J

DOB

Mar 07 1938 Mar 27 1942

Note: The display of DOB is controlled by the format for Personnel.DOB.

Example 3

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 '082542'(DATE FORMAT 'mmddyy')

ORDER BY dob ;

Teradata RDBMS for UNIX SQL Reference

5-15
Data Definition

DATE Numeric Data Type

Example 4

The format specification changes the display to the following:

Name DOB

Inglis C 38-03-07

Peterson J 42-03-27

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

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

and the display becomes:

Name DOB

Inglis C 380307

Peterson J 420327

Note the change in format on the WHERE statement simplifies the request.

Further examples illustrating arithmetic operations on DATE are listed below:

Statement: System returns:
SELECT DATE; Date
96/01/24
SELECT DATE +3; (Date+3)
96/01/27
SELECT DATE -3; (Date-3)
96/01/21
SELECT DATE - DATE; (Date-Date)
0

5-16

Teradata RDBMS for UNIX SQL Reference
Data Definition Arithmetic on Date Values

Introduction

ADD MONTHS Function

EXTRACT Function

Arithmetic on Date Values

The operations of addition and subtraction are allowed as follows, where integer values represent the number of days:
Previous << 1 .. 33 34 35 36 37 38 < 39 > 40 41 42 43 44 45 .. 241 >> Next