locked
Convert values with trailing zeroes RRS feed

  • 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?

     

     

     

    Wednesday, October 8, 2008 4:18 PM

Answers

  •  

    what is the datatype of accountnumber?

     

    try with bigint

     

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

     

    output

    10225780013

    Wednesday, October 8, 2008 4:28 PM

All replies

  •  

    what is the datatype of accountnumber?

     

    try with bigint

     

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

     

    output

    10225780013

    Wednesday, October 8, 2008 4:28 PM
  • 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!

     

     

    Wednesday, October 8, 2008 4:30 PM
  • INT can store only from -2,147,483,648 to 2,147,483,647

    So you will have use BIGINT here

     

     

    Wednesday, October 8, 2008 4:41 PM
  • Thank you.  Accountnumber datatype was nvarchar(50) and using bigint worked great!

     

     

    Wednesday, October 8, 2008 5:37 PM