Tools & Utilities

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-04-2019
12:43 PM

03-04-2019
12:43 PM

Hi There,

Want to understand how this formula exactly works

Trim(value(FORMAT '-(35)9.9(2)'))

when value is 0.1950000, the output is 0.20

when value is 0.5850000, the output is 0.58

can someone help me understand this please

Thanks

Nikhith

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-05-2019
01:35 AM

03-05-2019
01:35 AM

Hi Nikhith,

Yes, this one is not "natural" but it's fully documented :

Results of a FORMAT Phrase Defining Too Few Positions

Extract :

Rounding is based on “Round to the Nearest” mode, as illustrated by the following process.

- Let B represent the actual result.
- Let A and C represent the nearest bracketing values that can be represented, such that A < B < C.
- The determination as to whether A or C is the represented result is made as follows
- When possible, the result is the value nearest to B.
- If A and C are equidistant (for example, the fractional part is exactly .5), the result is the even number.

1 ACCEPTED SOLUTION

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-05-2019
01:35 AM

03-05-2019
01:35 AM

Hi Nikhith,

Yes, this one is not "natural" but it's fully documented :

Results of a FORMAT Phrase Defining Too Few Positions

Extract :

Rounding is based on “Round to the Nearest” mode, as illustrated by the following process.

- Let B represent the actual result.
- Let A and C represent the nearest bracketing values that can be represented, such that A < B < C.
- The determination as to whether A or C is the represented result is made as follows
- When possible, the result is the value nearest to B.
- If A and C are equidistant (for example, the fractional part is exactly .5), the result is the even number.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-05-2019
03:43 AM

03-05-2019
03:43 AM

Re: Format '-(35)9.9(2)'

Thanks Waldar,

Thanks for listing down the points, I know exactly understand what's happening. My brains were literally going off, for this one :)

Regards

Nikhith

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-05-2019
07:31 AM

03-05-2019
07:31 AM

Re: Format '-(35)9.9(2)'

Just for completeness:

*Round Half Even* (as described) is the Teradata default method for rounding FLOAT and DECIMAL.

But by default, NUMBER uses the *Round Half Away From Zero* method (also known as "commercial rounding") instead.

And there are dbscontrol options to use *Round Half Away From Zero* for DECIMAL, or to use *Round Half Even* for both DECIMAL and NUMBER .