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

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
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.