CODEWITHSUNDEEP
c#sqlsql-server
Harry
Harry

Reputation: 3116

SQL Server rounding issue on insert

I have an application that computes a value of -1407.858; the C# data type for this is decimal.

The column data type in the database is decimal(12,2). I would've thought it'd round the value and insert -1407.86 (which would satisfy both away from zero rounding and to even rounding).

Unfortunately the value is being inserted as -1407.85. The only explanation I can come up for this is that the last digit is being truncated.

I have done some simple test queries in SQL Server such as:

declare @first AS decimal(12,2)
declare @second AS float --[or decimal(12,3)]

set @second = -1407.858
set @first=@second

select @first;

And I get back -1407.86, so I suppose SQL Server automatically rounds values when it's setting them, but not when inserting them. Is this correct? Does SQL Server only truncate when inserting, but round when setting?

Upvotes: 1

Views: 5155

Answers (3)

Adilson de Almeida Jr
Adilson de Almeida Jr

Reputation: 2755

I did a test with Linq2sql, with the following situation:

  • Mapped a table with a int primary key, and a decimal(12,2) field;
  • Inserted a item with the value -12.585;

Executing SQL Profiler, I realized linq2sql has truncated the value, sending the following statement to server:

exec sp_executesql N'INSERT INTO [dbo].[TestNumbers]([Id], [Number1])
VALUES (@p0, @p1)',N'@p0 int,@p1 decimal(12,2)',@p0=0,@p1=-12.58

So you are facing a linq2sql bug. You should round your numbers before you send them.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453910

I suppose SQL Server automatically rounds values when it's setting them, but not when inserting them. Is this correct?

Nope.

DECLARE @T TABLE(C DECIMAL(12,2))
INSERT INTO @T VALUES (-1407.858)
SELECT * FROM @T

Returns -1407.86. I presume the truncation happens somewhere on the C# side.

Upvotes: 2

Andomar
Andomar

Reputation: 238296

A decimal(12,2) can store 12 digits total, of which two are behind the comma. So it's correct that -1407.858 is rounded to -1407.85.

Upvotes: 0

Related Questions