b¬äd†ødd

Numeric Rounding

February 21, 2012 by Brad

A question that was posed to me over a year ago has come back around: What is the function in Teradata to round numeric values?

The short answer is that there isn't one, per se. It is performed by CASTing a value to desired precision via the DECIMAL(n,m) data type. However, there is a twist--in Teradata a DBS Control Setting determines how rounding occurs. It is the RoundHalfwayMagUp value which controls values that end with the digit 5--do they round up or down? Mathematically, we used to always round ending values of 5 up. This is not necessarily the case here.

I have used machines with RoundHalfwayMagUp set to FALSE. This means that ending-significant-digit-5 values will round up or down depending on whether the rounding digit is even or odd. If odd, the value increases; an even value and it remains status quo.

An example: We want to round money values with cents to whole dollars. Values with zero to forty-nine cents will always round down; fifty-one to ninety-nine cents always round up. But, values with exactly fifty cents can go either way. Values with an even dollar amount will round down, and an odd dollar amount will round up.

POC: our volatile table (remember, anyone with a Teradata ID can create a volatile table):

create volatile table foo, no log (
 	id  integer      not null,
 	val decimal(9,2) not null)
unique primary index (id)
on commit preserve rows
;

We load in our test data:

insert into foo (1,1.00);
insert into foo (2,0.99);
insert into foo (3,1.99);
insert into foo (4,0.49);
insert into foo (5,0.50);
insert into foo (6,1.50);

And, query it accordingly:

select
    id,
    cast(val        as integer)    as truncated_value,
    cast(val        as decimal(9)) as rounded_value,
    cast(val + .001 as decimal(9)) as new_rounded_value

from foo

order by 1
;

Which results in

ID 	truncated_value 	rounded value 	new_rounded_value
1  	1                 	1            	1
2  	0                 	1            	1
3  	1                 	2            	2
4  	0                 	0            	0
5  	0                 	0               1
6  	1                 	2            	2

Observations:

• The truncated value merely strips away the cents. This is accomplished by CASTing the value into the INTEGER family of data types.
• The rounded values, for fifty-cent values, are dependent on whether the whole-dollar is even or odd. Even values round down; odd values round up. Note: record 5 contains $0.50 and it rounds down to $0; whereas, record 6 contains $1.50 and rounds up to $2. This is because the $1 in record 6 is odd, thus $1.50 rounds up to $2.
• If you always want the fifty cent value to round up, the new rounded value shows the technique to make that occur. Add $0.001 (one precision point past the DECIMAL cent precision) to the value, and then round via the CAST to DECIMAL. It takes the $0.50 value in record 5 and converts it to $0.501, and then rounds, thereby producing $1 instead of $0.

I hope this helps with your future coding.

Cheers
-Brad