in black and white
Main menu
Share a book About us Home
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.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 45 46 47 48 49 50 < 51 > 52 53 54 55 56 57 .. 241 >> Next

When digits to the right of the least significant digit cannot be accommodated by the defined format, INTEGER values are truncated, and FLOAT or DECIMAL values are rounded.

The rounding mode is rounding to nearest.


Teradata RDBMS for UNIX SQL Reference
Data Definition

Numeric Formats

Japanese Character Site

European Character Site

Table 5-7

European Alternative Currency Symbols

Table 5-8

Japanese Alternative Currency Symbols

On a Japanese character site, a set of alternate currency symbols is available when the AltCurrency flag in the RDBMS ControlRecord is set ON.

On a European character site, alternative currency symbols also are available with the AltCurrency flag is ON. However, your user-defined character sets must map each external currency symbol to the appropriate Teradata RDBMS internal representation before the symbols can be used. The advantage is that any keyboard character can be created as a currency symbol by mapping it to one of the internal currency codes. (Contact your database administrator to determine if European alternative currency symbols are available.)

The symbols in each set are as follows:

External Symbol Description
$ Dollar sign
? Cent sign
? Pound sign
? Yen sign
general currency General currency symbol

External Symbol (Any Kanji Character Set) Description
$ Dollar sign
? Yen sign

Teradata RDBMS for UNIX SQL Reference

Data Definition

Numeric Formats

An alternate currency symbol has the same formatting rules and properties as the standard dollar sign ($). That is, one symbol places the edit character in a fixed position of the output string; two or more symbols defined with Z’s (for zero-suppressed decimal digits) causes the symbol to float to the right, leaving no blanks in the output between the sign and the leftmost digit.

Activating alternate currency symbols does not affect the normal properties of the FORMAT phrase. For example, if alternate symbols are in effect, then the following statement:

SELECT 1 (FORMAT '$$Z,ZZ9.99'), 2 (FORMAT 'YYZ,ZZ9.99');

returns the following:

$1.00 Y2.00

Note that two currency symbols (for example, $ and ?) cannot be used together in the same FORMAT phrase.


Teradata RDBMS for UNIX SQL Reference
Data Definition

DATE Formats

DATE Formats


DATE formats consist of the characters shown in Table 5-9.

Table 5-9

DATE Format Characters

Characters Meaning
DMY Specifies both the input and output formats for dates. DMY indicates the day, month, and year, respectively.
DDD Specifies that date is represented in the format of sequential day in the year. For example, January 12 is represented as ’012’. DDD may be used with any combination of year and separator characters or blanks.
MMM Specifies that the month is to be abbreviated to 3 characters (OCT).
MM Specifies that the month is to be indicated as a 2-digit number.
/ Slash; used as a separator.
B Blank; used as a separator.
, Comma; used as a separator.
’ Apostrophe; used as a separator.
Colon; used as a separator.
Period; used as a separator.
- Dash; used as a separator.

If a field is declared as type DATE and a FORMAT phrase is FOrmat ConsistenCy Is specified, the data must be entered and retrieved in the specified

Enforced format, and it must be enclosed in apostrophes.

For example, data supplied to, or used in, a conditional WHERE or HAVING clause for the following column must be defined as ’OCT 24, 1985’.


If a field is to be compared with a DATE value, or inserted into a DATE Comparisons 5 column in DATE format, you must be sure that the contents of the

field match the default DATE format, or you must specify a date in the conversion that matches the expected content.

Teradata RDBMS for UNIX SQL Reference

Data Definition

DATE Formats

Table 5-10

DATE Format Results

For example, if the data is CHAR(6) in the form yymmdd, and a column date format is YYMMDD, the conversion works. If, however, the default DATE format (YY/MM/DD) is used, the conversion fails.

In this case, you can force the conversion from character to integer by including the INTEGER phrase. Numeric values can be converted into dates in the default format (YY/MM/DD), or you can force conversion to DATE with the format YYMMDD.

IF this field . . . Is in this form . . . THEN the conversion is . . .
CHAR(8) yy/mm/dd Direct.
yyyymmdd to DATE (FORMAT ’YYYYMMDD’)
CHAR(11) mmmdd,yyyy to DATE (FORMAT ’MMMDD,YYYY’).

Note: The recommended format for DATE, as the year 2000 approaches, is ‘YYYY-MM-DD’, which is also the only ANSI SQL format for a date. Formats using only the last two digits of the year will be a problem as the year 2000 is approached. For Example: ‘2000-01-01’. Formatted as ‘YY/MM/Dd’ this is 00/01/01 and so is ‘1900-01-01’.

Table 5-10 lists examples of formats that can be used to present a date.
Previous << 1 .. 45 46 47 48 49 50 < 51 > 52 53 54 55 56 57 .. 241 >> Next