Books in black and white
 Books Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

# Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Previous << 1 .. 62 63 64 65 66 67 < 68 > 69 70 71 72 73 74 .. 241 >> Next

Stage Process
1 The encoded date value is converted to an intermediate value which is the number of days since some fixed date
2 The integer value is then added or subtracted, forming another value as number of days, since the fixed base date
3 The result is converted back to a date, valid in the Gregorian calendar.

The Date - Date expression is not processed as a simple subtraction.

The process is as follows the encoded date values are converted to intermediate values which are each the number of days since a fixed date. The second of these values is then subtracted from the first, giving the number of days between the two dates.

Other arithmetic operations on date values may provide results, but those results are not meaningful.

Teradata RDBMS for UNIX SQL Reference

6-11
SQL Expressions

Date and Time Expressions

Example

Date/2 provides an integer result, but the value has no meaning.

There are no simple arithmetic operations that have meaning for time values. The reason is that a time value is simply a real number with time encoded as:

(HOUR*10000 + MINUTE*100 + SECOND)

where SECOND may include a fractional value.

6-12

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

The EXTRACT Function

The EXTRACT Function

Teradata SQL supports a subset of the ANSI SQL EXTRACT IntrOducUon function. Full ANSI SQL EXTRACT allows the extraction of any

field contained in any DateTime or INTERVAL value. We support EXTRACT of YEAR, MONTH, or DAY from Teradata date value, or HOUR, MINUTE, or SECOND from a Teradata REAL value, which contains a time as Teradata currently encodes time.

Syntax

The syntax for extracting the year, month, or day is:

-EXTRACT—(-

-YEAR— MONTH — — DAY —

-FROM — date_value) -

HH01A072

IF the date_value is . . . THEN . . .
given as a character string literal the literal must be of the form ‘YYYY-MM-DD’.
not a character string literal it must be the value of an expression that results in a Teradata DATE data type.
NULL the result is NULL.
not NULL the result is an integer.

The following example returns the year, as an integer, from the ExampIe1 current date.

EXTRACT (YEAR FROM DATE)

Assuming PurchaseDate is a DATE field, this example returns the Example 2 month of the date value formed by adding 90 days to PurchaseDate

as an integer.

EXTRACT (MONTH FROM PurchaseDate+90)

The following returns 12 as an integer.

Example 3

EXTRACT (DAY FROM '1996-12-12')

This example returns an error, found by trying to evaluate the Example 4 character string literal as a date.

EXTRACT (DAY FROM '1996-02-30')

Teradata RDBMS for UNIX SQL Reference

6-13
SQL Expressions

The EXTRACT Function

Example 5

EXTRACT HOUR, MINUTE, SECOND From a REAL Value

Example 1

Example 2

The following returns an error, found by trying to evaluate the character string literal as a date. In this case, the reason is that EXTRACT uses the ANSI format for recognizing a date as a character string literal.

EXTRACT (DAY FROM '96-02-15')

Except for character string literals, if the argument for this form of EXTRACT is a value of the Teradata RDBMS type DATE, the value contained is warranted to be a valid date, for which EXTRACT cannot return an error.

The syntax for extracting the hour, minute, or second is:

-EXTRACT—(-

-HOUR-

— MINUTE —

— SECOND —

-FROM — real_value) -

HH01A070

The current release of Teradata RDBMS does not support TIME as a separate data type, but does allow the storage of time values encoded in REAL values. The encoding is

(HOUR*10000 + MINUTE*100 + SECOND)

where SECOND is carried to two decimal places.

Current time is retrieved as the system value TIME, to the indicated precision.

Externally created values of time can be appropriately encoded and stored in a REAL column, to any desired precision as long as the encoding created a value representable by REAL without precision loss.

Since TIME is not a true data type in the current Teradata RDBMS, there is no warranty that a value passed to this form of EXTRACT contains a valid time. EXTRACT of HOUR, MINUTE, or SECOND, may thus return an error detectable only at execution time.

If the real_value is NULL, the result is NULL.

This example returns the hour, as an integer, from the current time.

EXTRACT (HOUR FROM TIME)

The following is not reliable because TIME + 50 means add 50 seconds to the current time, using real arithmetic. If the seconds field has a value greater than 09, say 37, the result will be 87 in that field, which is invalid. In this case, the value of (TIME + 50) is recognizably invalid and an error will be returned. If the seconds field has a value greater than 49, say 53, the result is 03 in that field, with the minute field incremented by one. In this case, the value of

6-14

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

The EXTRACT Function

(TIME + 50) looks like a valid time and no error will be detected, but the result is incorrect.
Previous << 1 .. 62 63 64 65 66 67 < 68 > 69 70 71 72 73 74 .. 241 >> Next