Rounding off in SQL Server

Question

I need to round off a number of data type numeric nearest to 2 places in SQL Server

Eg.,

Input:  123.10000000
Output: 123.10

Thanks and Regards,
Ismail

score 6 · Answer 1 · answered Feb 08 '11 at 08:46

6

You will need to convert it:

Select Convert(numeric(19,2), @value)

You can use the Round function too to perform the rounding:

Select Round(@Value, 2)

answered Feb 08 '11 at 08:46

codingbadger

Neil Knight · Answer 2 · 2011-02-08T09:05:24.283

2

Would CONVERT(DECIMAL(12,2), ROUND(123.10000000, 2)) do the trick?

edited Feb 08 '11 at 09:05

answered Feb 08 '11 at 08:47

Neil Knight

1

@Andriy - `money` converts it to 4 decimal places though doesn't it? So the output would be 123.1000 – codingbadger Feb 08 '11 at 10:34
@Barry: No, it wouldn't as long as the value is rounded to 2 decimal places. – Andriy M Feb 08 '11 at 10:46
@Andriy & Barry: Can I draw your attention to this post http://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server – Neil Knight Feb 08 '11 at 10:54
@Ardman: Thank you. `money` has got a fixed and quite limited precision, that is a fact well documented. Behind the stage `money` is essentially a 64-bit integer whose last 4 digits are simply displayed as decimals. Being aware of that helps people to be cautious when manipulating such data. Being unaware of that causes people to call it 'pure garbage'. – Andriy M Feb 08 '11 at 12:31

score 1 · Answer 3 · answered Feb 08 '11 at 08:47

1

You can try:

select round(123.10000000, 2)

or

select cast(123.10000000 as decimal(12,2))

answered Feb 08 '11 at 08:47

dogbane

Your second approach now seems to me the best one, as it both gets rid of the extra decimal places and, as it turns out, implicitly rounds the value. (I first thought it would truncate it.) – Andriy M Feb 08 '11 at 14:13

score 1 · Answer 4 · answered Feb 08 '11 at 08:48

1

SELECT CAST(ROUND(InputValue, 2) AS money) AS OutputValue

answered Feb 08 '11 at 08:48

Andriy M

4 Answers4