Can any one here please advise how to add a decimal point after first digit while selecting numeric value from database?

Example: Output is 8250 should be 8.250

I tried this:

convert(decimal(10,3),ti.Rate/1000) AS Amount

but the output returns the wrong value of 8.000. It should be 8.250.

2 Answers 2

DECLARE @value INT = 8250

PRINT convert(DECIMAL(10, 3), @value / 1000.00)



Because all the values inside the convert call are integers it is performing integer division to get an integer result before converting to a decimal. ti.Rate/1000 is 8250/1000 which with integer division becomes 8. CONVERT then changes the type but the extra precision is already lost.

If you add a decimal place to the numeric literal 1000, SQL Server will see it as a decimal/numeric type number instead of an integer, so all you need to do is CONVERT(DECIMAL(10,3),ti.Rate/1000.0) AS Amount. Dividing an integer by a decimal/numeric (or vice-versa) results in a decimal/numeric.

In fact you can probably just do ti.Rate/1000.0 AS Amount unless you specifically need the type to be exactly DECIMAL(10,3) instead of the default (a quick test on SQL2016 shows the default to be NUMERIC(17,6), note that numeric and decimal are functionally identical as one is a synonym for the other). Or if you do specifically need DECIMAL(10,3), you might avoid extra implicit conversions by converting one of the inputs instead of the output: CONVERT(DECIMAL(10,3),ti.Rate)/1000.0 AS Amount (though the performance difference here is likely to be at best infinitesimal).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.