## Message Posted: Wed, 19 May 2004 @ 13:54:15 GMT

 < Last>>

 Subj: Re: Rounding vs truncation From: Geoffrey Rommel

 How do I get an integer result from the division of two integers or currency/decimal divided by an integer that is ROUNDED vs. truncated? i.e., select 10/4 results in 2 - I want the 2.5 to round to 3.

Well, you've opened a real can of worms here. Division of two integers truncates, so in order to get rounding you have to cast at least one expression to a non-integer type *before* dividing. Decimal division is a hybrid written by Teradata to follow their own rules, including (by default) rounding to the even number. Consider these results.

```     create table round_test
(int01   integer,
dec01   decimal(9,2) )
unique primary index(int01);

*** Table has been created.
*** Total elapsed time was 1 second.

ins round_test (2, 2.0);
ins round_test (6, 6.0);
ins round_test (10, 10.0);
[etc.]

select int01, dec01,
int01 / 4  truncated,
(dec01 / 4.0) (decimal(9,0))  "rounded?"
from round_test
order by 1;

*** Query completed. 7 rows found. 4 columns returned.
*** Total elapsed time was 1 second.

int01        dec01    truncated     rounded?
-----------  -----------  -----------  -----------
2         2.00            0           0.
6         6.00            1           2.
10        10.00            2           2.
14        14.00            3           4.
18        18.00            4           4.
22        22.00            5           6.
26        26.00            6           6.
```

Above, we convert our integer to decimal, divide by 4.0, and then cast the result to decimal(9,0). The last cast forces rounding. As you can see, the rounding goes to the even number. If you want to round up, as usually taught in grade school, you must change a DBS Control setting or do your own calculation, thus:

```     select int01, dec01,
(((dec01 / 4.0) + 0.5) (integer)) / 1  "rounded!"
from round_test
order by 1;

*** Query completed. 7 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

int01        dec01     rounded!
-----------  -----------  -----------
2         2.00            1
6         6.00            2
10        10.00            3
14        14.00            4
18        18.00            5
22        22.00            6
26        26.00            7
```

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback