Microsoft Sql Server and the Decimal Data Type

[BobInNJ]

It is my understanding that the decimal data type in Microsoft SQL Server should be viewed as a base 10 floating point number. Please correct me if that is wrong. Therefore, if I have a field defined as DECIMAL(4,2) called v1 (defined in a table t1) then I should be able to store the number 123.4 in v1. However, the following insert statement does not work: insert into t1 values ( 123.4 ) I would like to know why. Thanks Bob

[J4amieC]

BobInNJ wrote:

insert statement does not work

Specifics please. define "Does not work".

[BobInNJ]

By now working, I mean I get the following error: Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated. Bob

[Lost User]

What's the precision of your decimal data type? That could be the cause of the overflow. The code below seems to work, can you verify it on your machine?;

DECLARE @TestTable TABLE(
Column1 DECIMAL(18,2))

INSERT
INTO @TestTable
(Column1)
VALUES (123.4)

SELECT *
FROM @TestTable

Using the default precision, two decimals. Might it be that your culture-settings have something else defined for the decimal separator?

I are Troll :suss:

[BobInNJ]

Eddy, Thanks for the response. I tried your example and it worked. However, your example defines the field as 18,2 not 4,2 as I defined it. I am starting to think that the decimal data type should be thought of as a fixed point data type. That is, when I define a decimal data type as 4,2 that means exactly two digits to the right of the decimal point, not two or less. Do I have this right? Bob

[The Man from U N C L E]

Select Convert(DECIMAL(4,2), 123.4)

As you have said this does not work, giving an arithmetic overflow error. The problem is that while the precision of 4 does give you 4 digits to work, with you have specified a scale of 2. This means 2 of the digits must occur AFTER the decimal point. Therefore a Decimal(4,2) will accept any number between -99.99 and +99.99 with two decimal places. For more info check out the MSDN[^] web site.

If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk

[Lost User]

BobInNJ wrote:

when I define a decimal data type as 4,2 that means exactly two digits to the right of the decimal point, not two or less. Do I have this right?

That would be padded with zeroes, giving you 123.40. If you try to insert 123.4 into a DECIMAL(4,2), then it will expect a maximum of 2 digits in front of the decimal separator, since the precision says that there will be 2 digits behind the decimal separator. Thus giving you this layout; "nn.dd" "123.40" will not fit, as the part on the left side of the decimal separator flows over the reserved amount of two digits. In other words; "123.4" = DECIMAL(4,1) "123.40" = DECIMAL(5,2) "23.40" = DECIMAL(4,2) "23.4" = DECIMAL(3,1)

I are Troll :suss:

[Luc Pattyn]

AFAIK decimal(4,2) can hold the range (-99.99,+99.99), and not 123.4 :)

Luc Pattyn

---

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

---