Post reply

conversion error

  • December 7, 2011 at 10:18 am

    #251758

    Hello,

    I have a column with datatype varchar ,some columns are like 19,345.00,123.78,23.00,123,234,12.00

    while converting this column into decimal(18,2) i am getting conversion error

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to numeric.

    Can anyone please help me out.

    Thank you

  • December 7, 2011 at 10:21 am

    #1418266

    Are you using the convert or the cast syntax?

  • December 7, 2011 at 10:24 am

    #1418269

    Just replace all spaces, commas (assuming period is your decimal separator).

    Then the cast will work.

  • December 7, 2011 at 10:34 am

    #1418277

    if you convert to MONEY datatype inbstead of decimal, the data will convert cleanly;

    otherwise, as Ninja stated, you have to remove the non-numeric strings like commas and spaces.

    with MySampleData (TheValue)

    AS

    (

    SELECT '19,345.00' UNION ALL

    SELECT '123.78' UNION ALL

    SELECT '23.00' UNION ALL

    SELECT '123,234' UNION ALL

    SELECT '12.00'

    )

    --select convert(decimal(19,4),TheValue) --<--fails!

    select convert(money,TheValue)

    from mySampleData

    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!

  • December 7, 2011 at 11:09 am

    #1418302

    thank you friends.

    with the Replace function, my convert function is working fine.

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

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