-- Converting string of twenty 9's to bigint fails
select val_1 = convert(bigint,'99999999999999999999')
Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type bigint.


-- Converting string of twenty 9's to numeric(20,0) is sucessful
select val_2 = convert(numeric(20,0),'99999999999999999999')

val_2                  
---------------------- 
99999999999999999999



-- Example of using ISNUMERIC to check for valid values
select	val_1 = convert(numeric(20,0), 
                case when isnumeric(val) <> 1 then null else val end)
from 
     (select val = '99999999999999999999' ) a

val_1                  
---------------------- 
99999999999999999999


-- Example of using ISNUMERIC to check for invalid values, 
-- and return a null when it cannot be converted
select	val_2 = convert(numeric(20,0),
                case when isnumeric(val) <> 1 then null else val end)
from 
     (select val = 'xx999999999999999999' ) a

val_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 1
Error converting data type varchar to bigint.

I checked the varchar value and its in the range of bigint
Any ideas how to solve this will be appreciated
thanks
dolly

---

SELECT 
	@int = 320742987.070,
	@decimal = 320742987.070

SELECT @int, CAST(@int AS DECIMAL(12,3)), @decimal