Error converting data type varchar to numeric

  • Hello,

    I have a problem in one of my production server database.

    when i execute following query in production database

    select TOP 1 Holder_Id,Customer_Account_No from Properties where Original_Dollar_Amt = convert(decimal(15,2),389.83)

    and Convert(decimal(15,0),Serial_No) = 128249

    it is giving following error:

    Server: Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to numeric.

    The same query executing successfully in Dev server. Both table properties are same on Prod and Dev.

    Steps Tried:

    When i add "trim" string function to the query

    "select TOP 1 Holder_Id,Customer_Account_No from Properties where Original_Dollar_Amt = convert(decimal(15,2),389.83) and Convert(decimal(15,0),ltrim(Serial_No)) = 128249"

    query executing successfully and fetching records.

    Exported table to the same server on another database query executing successfully and fetching records without "trim" string function.

    Both SQL versions are same on Production and Development servers.

    Both are 8.00.2282

    Serial_No is Varchar(20) data type,

    I am trying to convert varchar into decimal data type.

    Appricate your help


    Kindest Regards,

    klss

  • It depends on the data stored in the table. Try running this:

    SELECT Original_Dollar_Amt , Serial_No FROM Properties where ISNUMERIC(Original_Dollar_Amt) = 0 OR ISNUMERIC(Serial_No) = 0

    Does it return rows? If so, fix the values and there you are.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • clean and crisp solution

    http://www.a2zmenu.com/MySql/Arithmetic-overflow-error.aspx

Viewing 3 posts - 1 through 2 (of 2 total)

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