Post reply

Arithmetic overflow error converting varchar to data type numeric.

  • August 19, 2008 at 7:42 am

    #72903

    select CAST('0061' as numeric(4,3))

    This is silly but I'm stumped by this statement giving me an error.

    Ultimately what I want is to insert '0061' in a column of type numberic(4,3). So I can't change the type, field length, or precision.

    Any help would be greatly appreciated.

    Ryan

  • August 19, 2008 at 9:09 am

    #860075

    Hi Ryan,

    A numeric(4,3) means that the total length of the value is 4 and the precision is 3. An example of this is 4.014.

    With this in mind how would you want your value of 0061 to appear? As 0.061?

    If so, you could use STUFF to do what you need

    SELECT CAST(STUFF('0061',2,0,'.') as numeric(4,3))

    Adam

  • August 19, 2008 at 9:14 am

    #860078

    ryansegg (8/19/2008)

    select CAST('0061' as numeric(4,3))

    This is silly but I'm stumped by this statement giving me an error.

    Ultimately what I want is to insert '0061' in a column of type numberic(4,3). So I can't change the type, field length, or precision.

    Any help would be greatly appreciated.

    Ryan

    numeric 4,3 would have a maximum value of 9.999 (61.000 would cause an overflow in this example)

    numeric 5,3 would have a maximum value of 99.999 (61.000 fits in this example)

    select CAST('0061' as numeric(5,3))

    it seems like you are using xxxx.000, so you'd need a numeric 7,3 at a minimum.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 19, 2008 at 11:27 am

    #860179

    Great. Thank you. "STUFF" is what I needed.

  • August 19, 2008 at 12:39 pm

    #860213

    DECLARE@data VARCHAR(4)

    SET@data = '0061'

    SELECTCAST(@Data AS INT) / 1000.0

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply