MS SQL Server decimal data type rounds up

Question

I need to be able to store numbers like 3.5 in my table. So I've used the decimal type field. But if I enter 3.5 it round it up to 4. Am I being stupid or is it not the point of decimal to have a decimal point?

Why you do not use FLOAT, And if You need to round always numbers to 0.0 or 0.5 take look at this thread — adopilot
– adopilot, Commented Apr 6, 2010 at 8:30
yup that worked, I just though FLOAT was for massive numbers. Thinks I need to get my head around data types again. thanks adopilot! — iamjonesy
– iamjonesy, Commented Apr 6, 2010 at 8:34
@Jonesy - just make sure you understand the difference between FLOATs and DECIMALs as they are not the same. FLOAT is approximate: msdn.microsoft.com/en-us/library/ms173773.aspx vs DECIMAL reference: msdn.microsoft.com/en-us/library/ms187746.aspx — AdaTheDev
– AdaTheDev, Commented Apr 6, 2010 at 8:39
Can you post the full definition of the decimal column (i.e. the scale and precision you've used) e.g. DECIMAL(10, 2) - 10=precision, 2=scale. Also, how are you entering the number - into a UI? In an SQL statement? Could you post that up too? — AdaTheDev
– AdaTheDev, Commented Apr 6, 2010 at 8:41
Float is a very bad idea if you plan to do math onthe value. It will cause rounding errors as it is not an exact value. Very bad recommendation. — HLGEM
– HLGEM, Commented Apr 6, 2010 at 14:06

st78 · Accepted Answer · 2020-09-15 20:16:53Z

16

You need to declare it like decimal(18,3) to specify the number of digits after the point.

In case you are using stored procedure parameter also must have precision specified next to decimal, e.g. decimal(18,3)

edited Sep 15, 2020 at 20:16

answered Apr 6, 2010 at 8:33

st78

7,84612 gold badges56 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iamjonesy Over a year ago

yeah it was declared as decimal(18,3). Not sure why I rounded it.

st78 Over a year ago

How did you put data? Maybe you are using stored procedure - than parameter also should have type decimal(18,3), not just decimal.

Collectives™ on Stack Overflow

MS SQL Server decimal data type rounds up

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related