Examples | FORMAT Phrase and NUMERIC Formats | Teradata Vantage - 17.10 - Examples: Rounding - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

The following SELECT statement returns 48 and 48.

SELECT 47.5(FORMAT 'zzzz'), 48.5(FORMAT('zzzz')) ;

A FORMAT clause can cause the number to be rounded; for example, consider the following:

SELECT 1.3451 (FORMAT 'zz.z');

The FORMAT clause operates on 1.3451 and returns 1.3.

SELECT 13451 / 10000.000

1.345 is returned as expected.

SELECT 13451 / 10000.000 (FORMAT 'zz.z');

The arithmetic expression is evaluated first (yielding 1.345), then the FORMAT clause is applied, returning 1.3.

SELECT 13451 / 10000.00 (FORMAT 'zz.z');

Note that two ‘roundings’ occur; first to 1.35 when evaluating the expression, then a second rounding to 1.4 as a result of the FORMAT statement.

To avoid double ‘roundings’, extend the precision of the arithmetic expression two decimal places beyond that of the FORMAT clause.