SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 converting varchar to bigint
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

dolly
Starting Member

3 Posts

Posted - 01/19/2005 :  17:06:53  Show Profile
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

clarkbaker1964
Constraint Violating Yak Guru

USA
428 Posts

Posted - 01/19/2005 :  17:09:09  Show Profile  Visit clarkbaker1964's Homepage
Maybe one of your entries has a space in the front try trimming the string with LTrim

Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 01/19/2005 :  17:11:43  Show Profile
isnumeric(var)
can be useful.

rockmoose
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/20/2005 :  00:04:30  Show Profile
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 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



Codo Ergo Sum
Go to Top of Page

dolly
Starting Member

3 Posts

Posted - 01/20/2005 :  16:42:50  Show Profile
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.070
Can you tell me how to proceed on this
thanks
dolly
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 01/20/2005 :  16:44:55  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 01/20/2005 :  18:59:12  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
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.070

SELECT @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.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000