Teradata SQL Quick Reference Guide: Simplicity by Design, Second
Edition
by Tom Coffing, Michael Larkins and Steve Wilmes
Coffing Data Warehousing. (c) 2006. Copying Prohibited.
Reprinted for Anil Kumar Gupta, IBM
anilkugu@in.ibm.com
Reprinted with permission as a subscription benefit of Books24x7,
http://www.books24x7.com/
All rights reserved. Reproduction and/or distribution in whole or in part in electronic,paper or
other forms without written permission is prohibited.
TeradataSQLQuickReferenceGuide:SimplicitybyDesign,SecondEdition
Appendix C: Data Types and Format Symbols
Overview
The following table is a chart of all the ANSI Standard Data Types that Teradata supports.
Data Type
Description
Data Value Range
INTEGER
Signed whole number
-2,147,483,648 to 2,147,483,647
SMALLINT
Signed smaller whole number
-32,768 to 32,767
DECIMAL (X,Y)
Signed decimal number 18 digits on either side of the decimal
point
Largest value DEC(18,0)
Synonym for DECIMAL
Same as DECIMAL
FLOAT
Floating Point Format (IEEE)
<value>10307to<value>10-308
REAL
Stored internally as FLOAT
PRECISION
Stored internally as FLOAT
DOUBLE PRECISION
Stored internally as FLOAT
CHARACTER (X)
Fixed length character string, 1 byte of storage per character,
1 to 64,000 characters long, pads to length with
space
Variable length character string, 1 byte of storage per character,
plus 2 bytes to record length of actual data
1 to 64,000 characters as a maximum. The system
only stores the characters presented to it
DATE
Signed internal representation of YYYMMDD (YYY represents
the number of years from 1900, i.e. 100 for Year 2000)
Currently to the year 3500 as a positive number and
back into AD years as a negative number.
TIME
Identifies a field as a TIME value with Hour, Minutes and
Seconds
TIMESTAMP
Identifies a field as a TIMESTAMP value with Year, Month, Day,
Hour, Minute, and Seconds
Where: X=1 thru 18, total number
of digits in the number
Smallest value DEC(18,18)
And Y=0 thru 18 digits to the right
of the decimal
NUMERIC (X,Y)
Same as DECIMAL
CHAR (X)
Where: X=1 thru 64000
VARCHAR (X)
CHARACTER VARYING (X)
CHAR VARYING (X)
Where: X=1 thru 64000
Teradata also has its own data types that are acknowledged as Teradata Extensions:
Data Type
Description
Data Value Range
BYTEINT
Signed whole number
-128 to 127
BYTE (X)
Binary
1 to 64,000 bytes
Variable length binary
1 to 64,000 bytes
LONG VARCHAR
Variable length string
64,000 characters (maximum data length) The system only stores the characters
provided, not trailing spaces.)
GRAPHIC (X)
Fixed length string of 16-bit bytes (2 bytes per
character)
1 to 32,000 KANJI characters
Variable length string of 16-bit bytes
1 to 32,000 characters as a maximum. The system only stores characters provided.
Where: X=1 thru
64000
VARBYTE (X)
Where: X=1 thru
64000
Where: X=1 thru
32000
VARGRAPHIC (X)
Where: X=1 thru
32000
Format Symbols
Formatting symbols are very powerful. These symbols allow the user to control the way data can be inserted into a row and displayed in the
result set. It provides for more predictable result sets.
Page 2 / 3
Reprintedforibm\anilkugu@in.ibm.com,IBM
CoffingDataWarehousing,CoffingPublishing(c)2006,CopyingProhibited
TeradataSQLQuickReferenceGuide:SimplicitybyDesign,SecondEdition
Hint - Man Be sure to keep in mind that the ODBC will ignore formatting, unless you trick it. To see true formatting, the user should use
BTEQ.
Basic Numeric and Character Data Formatting Symbols
Symbol
X or x
9
Mask character and how used
Character data. Each X represents one character. Can repeat value- i.e. XXXXX or X(5).
Decimal digit. Holds place for numeric digit for a display 0 through 9. All leading zeroes are shown if the format mask is longer than the data value.
Can repeat value- i.e. 99999 or 9(5).
V or v
Implied decimal point. Aligns data on a decimal value. Primarily used on imported data without actual decimal point.
E or e
Exponential. Aligns the end of the mantissa and the beginning of the exponent.
G or g
Graphic data. Each G represents one logical (double byte- KANJI or Katakana) character. Can repeat value- i.e. GGGGG or G(5).
Advanced Numeric and Character Formatting Symbols
Symbol
Mask character and how used
Fixed or floating dollar sign. Inserts a $ or leaves spaces and moves (floats) over to the first character of a currency value. With the proper keyboard,
additional currency signs are available: Cent, Pound and Yen.
Comma. Inserted where appears in format mask. Used primarily to make large numbers easier to read.
Period. Primary use to align decimal point position. Also used for: dates and comma in some currencies.
Dash character. Inserted where appears in format mask. Used primarily for dates and negative numeric values. Also used for: phone numbers, zip
codes, and social security (USA).
Slash character. Inserted where appears in format mask. Used primarily for dates.
Percent character. Inserted where appears in format mask. Used primarily for display of percentage - i.e. 99% vs. .99
Z or z
Zero-suppressed decimal digit. Holds place for numeric digit displays 1 through 9 and 0, when significant. All leading zeroes (insignificant) are shown
as space since their presence does not change the value of the number being displayed.
B or b
Blank data. Insert a space where appears in format mask.
SELECT 'ABCDE' (FORMAT 'XxX') AS Fmt_Shorter
,2014859999 (FORMAT '999-999-9999') AS Fmt_Phone
,1021.53 (FORMAT 'ZZZZZZ9.9999') AS Z_press
,991001
(FORMAT '$$$$,$$$.99') AS Fmt_Pay ;
In addition to have the ability to format Numeric and Character Data Types, a user may also format Dates.
Date Formatting Symbols
Symbol
Mask character and how used (not case specific)
M or m
Month. Allows month to be displayed any where in the date display. When 'MM' is specified, the numeric (01-12) value is available. When 'MMM' is
specified, the three character (JAN-DEC) value is available.
D or d
Day. Allows day to be displayed any where in the date display. When 'DD' is specified, the numeric (01-31) value is available. When 'DDD' is
specified, the three-digit day of the year (001-366) value is available.
Y or y
Year. Allows day to be displayed any where in the date display. The normal 'YY' has been used for many years for the 20th century with the 19YY
assumed. However, since we have moved into the 21st century, it is recommended that the 'YYYY' be used.
SELECT
991001(date) (FORMAT 'Yyddd') AS Fmt_Julian
,991001(date) (FORMAT 'YYYY-mm-dd') AS Fmt_ANSI
,9991001(date) (FORMAT 'yy/mm/dd') AS Fmt_Teradata ;
Page 3 / 3
Reprintedforibm\anilkugu@in.ibm.com,IBM
CoffingDataWarehousing,CoffingPublishing(c)2006,CopyingProhibited
