# Teradata RDBMS forUNIX SQL Reference - NCR

**Download**(direct link)

**:**

**40**> 41 42 43 44 45 46 .. 241 >> Next

Argument 1 Operation Argument 2 Result

DATE + INTEGER DATE

DATE - INTEGER DATE

INTEGER + DATE DATE

DATE - DATE INTEGER

Note that adding 90 days, for example, is not identical to adding 3 months, because of the varying number of days in months. Also, adding multiples of 365 days is not identical to adding years because of leap years.

The ADD_MONTHS function provides for adding or subtracting months or years, handling the variable number of days involved.

The syntax for ADD_MONTHS is:

----ADD_ MONTHS —(date_ expression, integer)-

HH01A068

If ADD_MONTHS is applied to a date such as ‘1995-12-31’ and the resulting date is on a month with fewer days, the result is adjusted to the last valid day for that month. For example:

ADD_MONTHS ('1995-12-31', 2)

results in ‘1996-02-29’

Note: 1996 is a leap year.

To add or subtract n years to a date, use

ADD_MONTHS (date, 12*n)

Refer also to Chapter 6, “Date and Time Expressions”.

The EXTRACT function can be used to get the year, month, or day from a date. The result is an integer.

Refer to Chapter 6, “SQL Expressions,” in the section “The EXTRACT Function” for details.

Teradata RDBMS for UNIX SQL Reference

5-17

Data Definition

DECIMAL and NUMERIC Numeric Data Types

Introduction

DECIMAL and NUMERIC Numeric Data Types

The DECIMAL and NUMERIC data types represent a decimal number of n digits, with m of these digits to the right of the decimal point. The DECIMAL data type is synonymous with NUMERIC and can be abbreviated as DEC.

DECIMAL [(n[,m])]

I I I I I I I

I I I I I I I

1, 2, 4, or 8 bytes

I I I I I I I

Form

The specification of a DECIMAL data type is:

DECIMAL [(n[,m])]

The precision value (n) may range from 1 to 18; the scale value (m) may range from 0 to n. When the DECIMAL data type is specified with no values assigned for n and m, the default is DECIMAL(5,0). If no value is specified for m, the default is DECIMAL(n,0). This type corresponds to packed decimal on a client system.

Scaling

Decimal numbers are scaled by the power of ten equal to the number of fractional digits. The number is stored as a two’s complement binary number in one, two, four, or eight bytes. The number of bytes used for a decimal value depends on the total number of digits in that value.

The following list shows the number of bytes used to store decimal values.

FF19A002

Number of Digits Number of Bytes

1 to 2 1

3 to 4 2

5 to 9 4

10 to 18 8

In the following column definition, Salary is assigned the Example 5 DECIMAL data type:

Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99'

CHECK (Salary BETWEEN 1.00 AND 999000.00),

5-18 Teradata RDBMS for UNIX SQL Reference

Default Display Format Rules

Data Definition

DECIMAL and NUMERIC Numeric Data Types

The default DECIMAL or NUMERIC display format rule is:

- (n - m +1). 9 (m) where

n = total number of digits, m = decimal portion of the number, and n-m = integer portion of the number.

For example:

256.78 is type DEC (5,2) and the default format is:

‘- (4).9(2) or the equivalent ‘ —.99’

The total length is n+2, where +2 represents one additional position for the sign, and one additional position for the decimal point.

The default DECIMAL or NUMERIC display format is also described in Table 5-4, in the section, “Numeric Formats”, and in Table 5-11, in the section, “Data Default Formats”.

Teradata RDBMS for UNIX SQL Reference

5-19

Data Definition

BYTEINT Numeric Data Type

Introduction

Example

BYTEINT Numeric Data Type

The BYTEINT data type represents a signed binary integer (-2, -1, +1, +2, and so on) value in the range -128 to 127. A BYTEINT value occupies one byte.

Note: BYTEINT is flagged as non-ANSI if the SQL flagger is enabled.

BYTEINT

³ë

value range -128 to + 127

FF19A003

In the following column definition, EdLev is assigned the BYTEINT data type:

EdLev BYTEINT FORMAT 'Z9' CHECK (EdLev BETWEEN 0 AND 22) NOT NULL,

The default BYTEINT display format is -(3)9, or --- 9

The BYTEINT format is described in Table 5-4, in the section, “Numeric Formats”, and in Table 5-11, in the section, “Data Default Formats”.

5-20

Teradata RDBMS for UNIX SQL Reference

Data Definition

SMALLINT Numeric Data Type

Introduction

Example

SMALLINT Numeric Data Type

The SMALLINT data type represents a signed binary integer (-2, -1, +1, +2, and so on) value in the range -32768 to 32767. A SMALLINT value occupies two bytes.

SMALLINT

5?

CO

value range -32,768 to + 32,767

SMALLINT values are stored with the least-significant byte first.

In the following column definition, DeptNo is assigned the SMALLINT data type:

DeptNo SMALLINT FORMAT '999' BETWEEN 100 AND 900,

The default display format for SMALLINT is -(5)9 or---------9

Teradata RDBMS for UNIX SQL Reference

5-21

Data Definition

INTEGER Numeric Data Type

Introduction

Example

INTEGER Numeric Data Type

**40**> 41 42 43 44 45 46 .. 241 >> Next