Convert values with trailing zeroes

Question

I have a nvarchar(50) field that imported accountnumber data including decimal point and trailing zeroes.  I have the following query to update the field:

 

update ampfm.rpt_AdtVisit

set accountnumber = convert (int, convert(float, accountnumber))

 

This works fine on data like 111115269.00

 

However, some data read entries are 10225780013.000000 and I am receiving the following error:

 

Msg 232, Level 16, State 3, Line 13

Arithmetic overflow error for type int, value = 10225780013.000000.

The statement has been terminated.

 

How can I alter the update statement for these entries?

 

 

 

Answer 1

 

what is the datatype of accountnumber?

 

try with bigint

 

select convert (bigint, convert(float, '10225780013.000000'))

 

output

10225780013

Answer 2

This isn't an issue with the trialing zeros but a limitation in the size of an INT. Try using BIGINT.

 

http://msdn.microsoft.com/en-us/library/ms187745(SQL.90).aspx

 

HTH!

 

 

Answer 3

INT can store only from -2,147,483,648 to 2,147,483,647

So you will have use BIGINT here

 

 

Answer 4

Thank you.  Accountnumber datatype was nvarchar(50) and using bigint worked great!