Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dolly
Starting Member
3 Posts |
Posted - 2005-01-19 : 17:06:53
|
I am converting some varchar values(containing only numbers) of length 20 to bigint...convert(bigint,var)The statement worked fine before , but now after new inputs, it generates an error :Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to bigint.I checked the varchar value and its in the range of bigintAny ideas how to solve this will be appreciatedthanksdolly |
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-19 : 17:09:09
|
Maybe one of your entries has a space in the front try trimming the string with LTrim |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-01-19 : 17:11:43
|
isnumeric(var)can be useful.rockmoose |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-01-20 : 00:04:30
|
It may be that bigint is not large enough to convert the strings you are trying to convert. Try converting to numeric(20,0) to see if that works. As rockmoose said, you can use the ISNUMERIC function to check the data. See code examples below:-- Converting string of twenty 9's to bigint failsselect val_1 = convert(bigint,'99999999999999999999')Server: Msg 8115, Level 16, State 2, Line 3Arithmetic overflow error converting expression to data type bigint.-- Converting string of twenty 9's to numeric(20,0) is sucessfulselect val_2 = convert(numeric(20,0),'99999999999999999999')val_2 ---------------------- 99999999999999999999-- Example of using ISNUMERIC to check for valid valuesselect val_1 = convert(numeric(20,0), case when isnumeric(val) <> 1 then null else val end)from (select val = '99999999999999999999' ) aval_1 ---------------------- 99999999999999999999-- Example of using ISNUMERIC to check for invalid values, -- and return a null when it cannot be convertedselect val_2 = convert(numeric(20,0), case when isnumeric(val) <> 1 then null else val end)from (select val = 'xx999999999999999999' ) aval_2 ---------------------- NULL quote: Originally posted by dolly I am converting some varchar values(containing only numbers) of length 20 to bigint...convert(bigint,var)The statement worked fine before , but now after new inputs, it generates an error :Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to bigint.I checked the varchar value and its in the range of bigintAny ideas how to solve this will be appreciatedthanksdolly
Codo Ergo Sum |
|
|
dolly
Starting Member
3 Posts |
Posted - 2005-01-20 : 16:42:50
|
thanks everyone.. i tried ltrim, rtrim but no luck.. i tried to convert it to numeric(20,0) and it says arithmetic overflow.the value is definetly in the range of bigint .. as it is 320742987.070Can you tell me how to proceed on thisthanks dolly |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 16:44:55
|
320742987.070 - that isn't a bigint. Notice the decimals. Bigint doesn't support decimals. Use decimal or numeric. But change the precision and scale.Tara |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-20 : 18:59:12
|
That's a DECIMAL(12,3).Read Books Online for the definitions:Use the bigint data type to store numbers in the range from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes.Use the int data type to store numbers in the range from -2,147,483,648 through 2,147,483,647 only (requires 4 bytes of storage per value).Use the smallint data type to store numbers in the range from -32,768 through 32,767 only (requires 2 bytes of storage per value), and the tinyint data type to store numbers in the range from 0 through 255 only (requires 1 byte of storage per value).Also, note:DECLARE @int INT, @decimal DECIMAL(12,3)SELECT @int = 320742987.070, @decimal = 320742987.070SELECT @int, CAST(@int AS DECIMAL(12,3)), @decimal Based on the results of the above query and your previous posts, it would be advisable to study datatypes until you understand them.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|