numeric data type value
-
Hi My table has one column "vat_percentage" as numeric(2,2) data type. when I try to insert values using INSERT INTO [dbo].[cp_vat] ([vat_text],[vat_percentage],[vat_valid_from],[vat_valid_to]) values ('test',10.2,05/2005/05,05/05/2007) I get an error as Arithmetic overflow error converting numeric to data type numeric. can someone pls help me?
-
Hi My table has one column "vat_percentage" as numeric(2,2) data type. when I try to insert values using INSERT INTO [dbo].[cp_vat] ([vat_text],[vat_percentage],[vat_valid_from],[vat_valid_to]) values ('test',10.2,05/2005/05,05/05/2007) I get an error as Arithmetic overflow error converting numeric to data type numeric. can someone pls help me?
That will be your dates. Best practice is to use ISO format dates, yyyymmdd, as these are not sensitive to the selected locale, so you would write your statement as:
INSERT INTO [dbo].[cp_vat]
([vat_text],[vat_percentage],[vat_valid_from],[vat_valid_to])
values
('test',10.2,'20050505','20070505')Note that date literals are entered as strings, not numbers - if you pass a number, it will be treated as the binary representation and you'll get the wrong answer. If you're passing these dates in from application code, you should use parameterised queries and the appropriate Parameters collection. Stability. What an interesting concept. -- Chris Maunder
-
That will be your dates. Best practice is to use ISO format dates, yyyymmdd, as these are not sensitive to the selected locale, so you would write your statement as:
INSERT INTO [dbo].[cp_vat]
([vat_text],[vat_percentage],[vat_valid_from],[vat_valid_to])
values
('test',10.2,'20050505','20070505')Note that date literals are entered as strings, not numbers - if you pass a number, it will be treated as the binary representation and you'll get the wrong answer. If you're passing these dates in from application code, you should use parameterised queries and the appropriate Parameters collection. Stability. What an interesting concept. -- Chris Maunder
-
Mik, I used the string given by u, but still the error persists :( Arithmetic overflow error converting numeric to data type numeric.
What Mik said about date literals, is right. The precision is the total number of digits in the number, and the scale is the number of digits to right of the decimal point in the number. see here[^] You defined the field vat_percentage as numeric (2,2), means numeric field with precision 2 and scale 2. Now in this scenario the problem is with the numeric value that you given, which is 10.2, even if you enter 1 you will get the same error message. With your defined precision and scale you can only enter values like this: 0.23. If you entered .23987, it will round to .24 Or Alternatively change the field definition to numeric(4,2) that is total number of digits will be 4. Among them 2 digits will be at right of the decimal point. Hope this would solve your problem. Regards _____________________________ Success is not something to wait for, its something to work for.
-
What Mik said about date literals, is right. The precision is the total number of digits in the number, and the scale is the number of digits to right of the decimal point in the number. see here[^] You defined the field vat_percentage as numeric (2,2), means numeric field with precision 2 and scale 2. Now in this scenario the problem is with the numeric value that you given, which is 10.2, even if you enter 1 you will get the same error message. With your defined precision and scale you can only enter values like this: 0.23. If you entered .23987, it will round to .24 Or Alternatively change the field definition to numeric(4,2) that is total number of digits will be 4. Among them 2 digits will be at right of the decimal point. Hope this would solve your problem. Regards _____________________________ Success is not something to wait for, its something to work for.
-
Hi My table has one column "vat_percentage" as numeric(2,2) data type. when I try to insert values using INSERT INTO [dbo].[cp_vat] ([vat_text],[vat_percentage],[vat_valid_from],[vat_valid_to]) values ('test',10.2,05/2005/05,05/05/2007) I get an error as Arithmetic overflow error converting numeric to data type numeric. can someone pls help me?