15.10 - FORMAT - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

Controls the display of expressions, column data, and conversions between data types.

where:

 

Syntax element …

Specifies …

format_string

a variable length string enclosed in apostrophes containing formatting characters that define the display format for the data type.

FORMAT is a Teradata extension to the ANSI SQL:2011 standard.

Formats can be specified for columns that have character, numeric, byte, DateTime, Period, or UDT data types.

FORMAT pertains to data exported in report form, as is the case in BTEQ. FORMAT does not control internal storage representation of data or data returned in record or indicator variable mode.

Use the FORMAT phrase in a CREATE TABLE statement or ALTER TABLE statement to define the display format for a column. You can also use it in a retrieval statement to override the default format of a column or to define the display format of an expression. As such, it is both a Data Definition Language phrase and a Data Manipulation Language phrase.

A FORMAT specification can contain a maximum of 30 characters.

A FORMAT phrase can describe up to 18 digit positions (17 if the FORMAT contains an E). IEEE 64‑bit floating numbers are accurate to about 15 digits.

The output string that is produced as a result of a FORMAT phrase can have a maximum of 255 characters.

If you specify a FORMAT phrase to define the format for UDT columns, the format must be valid for the external type of the UDT, as specified by the transform that defines how to pass the UDT between the application and the server. For more information on transforms, see CREATE TRANSFORM in SQL Data Definition Language.

In addition to defining the default display formats for numeric, date, time, and timestamp data types, the Specification for Data Formatting (SDF) file defines strings that Teradata Database displays in place of certain formatting characters that appear in a FORMAT phrase.

For example, you can set the value of the Currency string in the SDF file to the currency symbol native to your locale. To include that currency symbol when you display numeric monetary information in an SQL SELECT statement, use the L formatting character in the FORMAT phrase.

For details on how to create an SDF file and convert the contents into an internal form usable by Teradata Database, see the description of the tdlocaledef utility in the Utilities book.

Examples of character string, numeric, and date formats appear in the following table.

 

FORMAT

Data

Display Form

X(6)

'HELLO'

'HELLO '

XXXXXX

'HELLO'

'HELLO '

X

'HELLO'

'H'

$$9.99

.079

' $0.08'

$$9.99

1095

******

ZZ,ZZ9.99

1095

' 1,095.00'

9.99E99

1095

'1.09E03'

999V99

123.456

'12346'

$(5).9(2)

1

'$1.00'

G--(8)D9(2)

-12345678.90

'-12 345 678,90'

where the SDF defines the group separator as ' ' (blank) and the radix separator as ','.

Z(I)D9(F)

000000.42

',42'

where the SDF defines the radix separator as ','.

YY.DDD

85.224

'85.224'

MMMBDD,BYY

Sep 12, 85

'Sep 12, 85'

YYYY-MM-DD

1996-02-14

'1996-02-14'

YYYYBMMMBDD

1985 Sep 12

'1985 Sep 12'

KatakanaEBCDIC with default format

Note that KatakanaEBCDIC uppercases single-byte data.

mN<ABC>b

MN<ABC>B
 

For more information on …

See …

the display format of character data types

“FORMAT Phrase and Character Formats” on page 297.

the display format of numeric data types

“FORMAT Phrase and NUMERIC Formats” on page 300.

the display format of DateTime data types

“FORMAT Phrase and DateTime Formats” on page 318.

the default data display formats

“Data Type Default Formats” on page 281.

default format examples

the descriptions of each data type in this volume.

using the FORMAT phrase in data conversions

“Data Type Conversions” in SQL Functions, Operators, Expressions, and Predicates.

obtaining the format of an expression or column

the FORMAT function in SQL Functions, Operators, Expressions, and Predicates.

using Kanji date and time markers to format DateTime data types

“FORMAT Phrase, DateTime Formats, and Japanese Character Sets” on page 326.

If the CREATE TABLE statement defined the format for the Salary column as ‘ZZ9999.99’, the result of the statement:

   SELECT SUM(Salary) FROM Employee;

is:

   SUM(Salary)
   -----------
     851100.00

The result of the following statement:

   SELECT SUM(Salary) (FORMAT '$$99,999.99') 
      FROM Employee;

is:

   SUM(Salary)
   -----------
   $851,100.00

FORMAT phrases, by themselves, cannot cause conversion of character to numeric data, or numeric to character data. An error message is returned if a FORMAT phrase implies data conversion.

Reconfigure the format of data returned:

   SELECT empno (FORMAT '99-999'), name, deptno 
   FROM employee; 

The system returns:

EmpNo       Name             DeptNo
------      ----------       ------
10-021      Smith T             700
10-007      Aguilar J           600
10-018      Russell S           300
10-011      Chin M              100

Another example of overriding the column default and formatting the output:

   SELECT salary (FORMAT '$$$,$$9.99') 
   FROM employee 
   WHERE empno = 10019 ; 

The system returns:

       Salary
       ------
   $28,600.00

You can use FORMAT as part of a derived expression. To determine the percent increase if employee 10019 is given a $1000 raise:

   SELECT (1000/salary)*100 (FORMAT 'zz9%')
      (TITLE 'Percent Incr') 
      FROM employee 
      WHERE empno = 10019 ;
   
   Percent Incr
   ------------
             3%