Rounding DECIMAL (14.3) to the third decimal digit in SQL 2008

Question

I need to round all incoming data with type DECIMAL(14,3) , which has 3 decimal digits to the last. I.e:.

 100; 100.0; 100.00; 100.000 -> 100 100.2; 100.02; 100.20; 100.22 -> 100.xx 

but

 100.221 -> 100.22 100.229 -> 100.22 

With which SQL statement can I verify that the remainder of the division in the decimal digit is greater than zero?

Answer 1

http://msdn.microsoft.com/en-us/library/ms175003.aspx

See section: Using Round to Truncate

Answer 2

With which SQL statement can I check this remainder of the division in decimal digit is greater than zero?

I don’t know exactly what you want to achieve, but if you want to find a way to calculate the decimal remainder, then the % (Modulo) method. It provides an integer remainder after dividing the first numeric expression by the second. So you can do it

1) SELECT 100.229 % 1;
GO

gives the result 0.229 .

2) SELECT (100.229 * 100) % 1;
GO

gives 0.900 . There you have the remainder of the decimal division.

But if you want to truncate, you can do as David B by suggesting using ROUND () to trim:

 SELECT ROUND(100.221, 2, 1); GO SELECT ROUND(100.229, 2, 1); GO