Rounding down DECIMAL(14,3) to third decimal digit

问题:

I have to round down all incoming data with type DECIMAL(14,3) which has 3 decimal digit to the last one. 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

Using which SQL operator can I check that residue of division in decimal digit is greater then zero?

回答1:

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

See the section: Using Round to Truncate

回答2:

Using which SQL operator can I check that residue of division in decimal digit is greater then zero?

I dont know exactly what you want to achieve with that but if you want a way to calculate the decimal residue, % (Modulo) is the way. It provides the integer remainder after dividing the first numeric expression by the second one. So, you can do this

1) SELECT 100.229 % 1;
GO

gives 0.229 as result.

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

gives 0.900 as result. There you have the residue from decimal division.

But if you want to truncate, you can do as David B suggested and use ROUND() to truncate:

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