inserting decimal values failing

[stockton]

I have setup the following table:-

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'theoreticalHold')
   DROP TABLE theoreticalHold
GO
CREATE TABLE THold (
pid int NOT NULL IDENTITY(1,1),
TS NUMERIC(2,2) NOT NULL,
TR NUMERIC(2,2) NOT NULL,
TB NUMERIC(2,2) NOT NULL,
TP NUMERIC(2,2) NOT NULL,
TP2 NUMERIC(2,2) NOT NULL,
filler1 NUMERIC(2,2) NOT NULL,
filler2 NUMERIC(2,2) NOT NULL,
filler3 NUMERIC(2,2) NOT NULL,
PRIMARY KEY (pid)
);

 

and now I am trying to execute the following insert statement in Enterprise Manager

INSERT INTO theoreticalHold  VALUES (5.40, 2.40, 1.00, 1.15, 2.40, 0,0,0);

and it fails with

Server: Msg 8115, Level 16, State 8, Line 1

Arithmetic overflow error converting numeric to data type numeric.

The statement has been terminated.

 

I do not understand the necessity of converting numeric to numeric.

Please tell me what I have done wrong.

[rhodesa]

when declaring a numeric datatype, the first number is max length of the number (including decimals) and the second is how many numbers can come after the decimal. so, 5.40 is 3 numbers. make your datatype definitions like so:

NUMERIC(3,2)

or better yet,

NUMERIC(5,2)

to be safe

[stockton]

Excellent, thank you. I would have looked at it for a year and still believe that 2,2 was two before and two after the decimal.