Rounding off in Teradata

General

Rounding off in Teradata

We have a requirement to Round up values in Teradata. Please find below the details for it and see if you can help us out on this.

In Teradata below is the output for a sample data.

1. SELECT CAST((2.1435) AS DECIMAL (7,3));     O/P - 2.144

2. SELECT CAST((2.1445) AS DECIMAL (7,3));     O/P - 2.144

3. SELECT CAST((2.1455) AS DECIMAL (7,3));     O/P - 2.146

Result expected for 2nd query is 2.145, but Teradata returns it as 2.144. Because halfway values are rounded down in Teradata which is not a situation in other databases (Ex. Oracle). In order to get the desired result we have added 0.0001 for rounding till 3 decimal places.

1. SELECT CAST((2.1435+0.0001) AS DECIMAL (7,3));     O/P - 2.144

2. SELECT CAST((2.1445+0.0001) AS DECIMAL (7,3));     O/P - 2.145

3. SELECT CAST((2.1455+0.0001) AS DECIMAL (7,3));     O/P - 2.146

We need to now expand this functionality and handle for any number of decimal places based on the source data. Kindly, let us know if there is any function available in Teradata V 12 which will be able to fetch this result by rounding up to required decimal places.

1 REPLY
Teradata Employee

Re: Rounding off in Teradata

There are many different approaches to "rounding", especially when it comes to values that are precisely halfway between two values in the desired result precision - see http://en.wikipedia.org/wiki/Rounding for an overview.

By default, Teradata uses "round half to even" when the system is initialized. If you prefer to always "round half away from zero", you can set the dbscontrol flag RoundHalfWayMagUp to True and restart the database. Note that this is a global setting and will apply to all future calculations.

TD14.0 (and later) includes a ROUND function that always does "round half away from zero" regardless of the dbscontrol setting.