none
Error converting data type varchar to numeric. -sql`

    Question

  • Hi , this is common error in sql. But i got this error when i try to do activeflag ( this column datatype is bit)=1. any idea how to solve this and what causing this error.
    Tuesday, May 22, 2012 8:38 PM

Answers

  • thanks for your time. we identified the issue. The issue is the with one view.

    QQ: when i select --> select * from view ..it is error out saying can not convert datatype varchar to numeric.  Is this possible ? the view is pointing to 2 tables.


    In view you might have where condition which is comparing column of different datatype. Here is a easy way to demonstrate.

    -- Repro Setup
    Use TempDB
    go
    Create Table C2_is_int ( c1 int, c2 int)
    go
    Insert into C2_is_int values (1, 1)
    go
    Create Table C2_is_char ( c1 int, c2 char(10))
    go
    Insert into C2_is_char values (1, 'Balmukund')
    go
    Create View Compare_Different_Column
    As
    Select i.c1,c.c2 from C2_is_int i, C2_is_char c
    where i.c2=c.c2
    go
    -- Error Repro
    Select * from Compare_Different_Column
    go
    --cleanup
    drop view Compare_Different_Column
    go
    drop table C2_is_int
    go
    drop table C2_is_char
    
    


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Proposed as answer by Naomi N Thursday, May 24, 2012 2:44 AM
    • Marked as answer by Stephanie Lv Wednesday, May 30, 2012 1:54 AM
    Thursday, May 24, 2012 1:55 AM
    Moderator

All replies

  • Hi kiranshiva,

    Could you please post the DDL table structure and query with sample data for reproducing this issue?

    The error message indicates that the value of varchar data type cannot be converted to numeric. You may consider using ISNUMERIC function to avoid this problem if the value is not qualified.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    Wednesday, May 23, 2012 1:44 AM
  • thanks for your time. we identified the issue. The issue is the with one view.

    QQ: when i select --> select * from view ..it is error out saying can not convert datatype varchar to numeric.  Is this possible ? the view is pointing to 2 tables.

    Wednesday, May 23, 2012 3:24 PM
  • kiranshiva,

    What is th query defined on your view? It will be helpful if you can provide the DDL statements and sample data here.


    Stephanie Lv

    TechNet Community Support

    Thursday, May 24, 2012 1:40 AM
  • thanks for your time. we identified the issue. The issue is the with one view.

    QQ: when i select --> select * from view ..it is error out saying can not convert datatype varchar to numeric.  Is this possible ? the view is pointing to 2 tables.


    In view you might have where condition which is comparing column of different datatype. Here is a easy way to demonstrate.

    -- Repro Setup
    Use TempDB
    go
    Create Table C2_is_int ( c1 int, c2 int)
    go
    Insert into C2_is_int values (1, 1)
    go
    Create Table C2_is_char ( c1 int, c2 char(10))
    go
    Insert into C2_is_char values (1, 'Balmukund')
    go
    Create View Compare_Different_Column
    As
    Select i.c1,c.c2 from C2_is_int i, C2_is_char c
    where i.c2=c.c2
    go
    -- Error Repro
    Select * from Compare_Different_Column
    go
    --cleanup
    drop view Compare_Different_Column
    go
    drop table C2_is_int
    go
    drop table C2_is_char
    
    


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Proposed as answer by Naomi N Thursday, May 24, 2012 2:44 AM
    • Marked as answer by Stephanie Lv Wednesday, May 30, 2012 1:54 AM
    Thursday, May 24, 2012 1:55 AM
    Moderator