Archives of the TeradataForum

Message Posted: Thu, 17 Nov 2005 @ 16:25:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Chaim:

As mentioned in a previous answer to your question, rounding in Teradata is done by casting the data to a decimal, even if a decimal with zero decimal places. If you cast to INT it is truncating the decimal and therefore not rounding.

I am not 100% sure, but I believe that someone has written a ROUND type of UDF and posted it to the download area if you wish to pursue that approach.

With that said, there is still another aspect regarding casting in Teradata rounding that you are probably not aware. By default Teradata does an accounting type of rounding. What that means is that when rounding and the data contains .5 exactly, it will round up to the next number when the digit being rounded is odd and it will round down when the digit being rounded is even.

For example:

     /* implied CAST syntax changes data type attribute */
     SEL 121.5(dec(3,0)) odd_num, 122.5 (dec(3,0)) even_num;

     odd_num           even_num
     -------           --------
         122                122

This default is established in the DBS Control record in a flag called ROUNDHALFWAYUPMAG. It is set to 'N' by default. Changing of it changes this behavior to allows round up when the number is exactly .5 verses .51.

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

	<Prev	Next>		<<First	<Prev	Next>	Last>>