none
Conversion error. Not understanding why is this error coming RRS feed

  • Question

  • select * from Item ii 
    where ii.QtyCommitted  >= 1 and ii.CategoryID <= 84 

    i ran the above query i am getting 

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value '99TUE' to data type int.

    Category ID is varchar(50) not null datatype and QtyCommitted is int,null

    Wednesday, December 4, 2019 3:50 PM

All replies

  • You can not compare the varchar with integer. What is the data format of CategoryID? Do you want to compare the number 99 from the varchar?

    A Fan of SSIS, SSRS and SSAS


    Wednesday, December 4, 2019 4:12 PM
  • Category ID is varchar(50) not null 
    Wednesday, December 4, 2019 4:24 PM
  • Why do you compare a varchar value to an integer? It does not work.
    Wednesday, December 4, 2019 4:31 PM
    Moderator
  • Maybe you wanted to write ‘where ii.QtyCommitted  >= 1 and ii.QtyCommitted <= 84’?

    Wednesday, December 4, 2019 4:36 PM
  • ?

    Maybe you wanted to write ‘where ii.QtyCommitted  >= 1 and ii.QtyCommitted <= 84’?

    Wednesday, December 4, 2019 4:42 PM
  • Hi,

    It failed because you have:  ii.CategoryID <= 84 that's integer comparison and its declared as varchar(50)  Maybe you want to do a chop and then convert to integer for comparison, then you need to provide a good set of data that covers all your data and we know where to chop the value. 

    Example:

    select * from Item ii 
    where ii.QtyCommitted  >= 1 and cast(substring(ii.CategoryID,1,2) as integer) <= 84 

    However does it run after 99 to 100 and up? Then you need to chop from first alpha char.




    • Edited by Soumen Barua Wednesday, December 4, 2019 4:56 PM
    Wednesday, December 4, 2019 4:52 PM
  • Do you want to compare 99 from the CategoryID? If yes, the numbers are always at the beginning of CategoryID?

    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 4, 2019 4:56 PM
  • its causing issue in report..same error
    Wednesday, December 4, 2019 5:47 PM
  • please explain with details
    Wednesday, December 4, 2019 5:53 PM
  • its causing issue in report..same error
    It is for sure that the query does not work since the string cannot be compared with the integer. But you need to tell us what you want to compare with. As I asked before, do you want to retrieve the records with the first two digits from CategoryID, which is less than or equal to 84? Tell us the details.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 4, 2019 6:00 PM
  • select * from Item ii 

    where ii.QtyCommitted  >= 1 and cast(substring(ii.CategoryID,1,2) as integer) <= 84 

    Above works fine in ssms now but i get same error in report.

    Wednesday, December 4, 2019 6:01 PM
  • User TRY_CAST instead of CAST.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 4, 2019 6:03 PM
  • Hi,

    Try this:

    select * from Item ii 

    where ii.QtyCommitted  >= 1 and cast(substring(c1,1,patindex('%[^0-9]%',c1)-1) as integer) <= 84 

    This will work if you have values starting with numbers, otherwise it will crash. We need to know a good representation of data in question. 

    Mark as answer if it works. Thanks






    • Edited by Soumen Barua Wednesday, December 4, 2019 6:21 PM
    Wednesday, December 4, 2019 6:08 PM
  • Wednesday, December 4, 2019 6:19 PM
  • select * from Item ii 

    where ii.QtyCommitted  >= 1 and try_cast(substring(c1,1,patindex('%[^0-9]%',c1)-1) as integer) <= 84 


    • Edited by Soumen Barua Wednesday, December 4, 2019 6:24 PM
    Wednesday, December 4, 2019 6:21 PM
  • Msg 537, Level 16, State 2, Line 42
    Invalid length parameter passed to the LEFT or SUBSTRING function.
    Wednesday, December 4, 2019 6:27 PM
  • Hi,

    select * from Item ii 

    where ii.QtyCommitted  >= 1 and try_cast(isnull(stuff(c1,patindex('%[^0-9]%',c1),len(c1),NULL),c1) as integer) <= 84 


    Wednesday, December 4, 2019 6:50 PM
  • that did not help
    Wednesday, December 4, 2019 6:58 PM
  • create table t1(c1 varchar(200))

    insert t1 values ('99TUE'), ('1'), ('TUE'), ('1999TU');


    select try_cast(isnull(stuff(c1,patindex('%[^0-9]%',c1),len(c1),NULL),c1) as integer)
    from t1;

    Mark as answer if it works. 




    • Edited by Soumen Barua Wednesday, December 4, 2019 7:04 PM
    Wednesday, December 4, 2019 7:01 PM
  • SELECT *
    FROM Item
    WHERE QtyCommitted >= 1 AND TRY_CAST(SUBSTRING(CategoryID,1,2) AS int) <= 84


    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 4, 2019 7:46 PM
  • Hi apsta,

    Please try following script. I create a function to get the number in your column .

    IF OBJECT_ID('DBO.GET_NUMBER') IS NOT NULL
        DROP FUNCTION dbo.GET_NUMBER;
    GO
    CREATE FUNCTION dbo.GET_NUMBER (@S VARCHAR(100))
    RETURNS VARCHAR(100)
    AS
    BEGIN
        WHILE PATINDEX('%[^0-9]%', @S) > 0
        BEGIN
            SET @S = STUFF(@S, PATINDEX('%[^0-9]%', @S), 1, '');
        END;
        RETURN cast(@S as int);
    END;
    go 
    IF OBJECT_ID('t1') IS NOT NULL drop table  t1   
    go 
    create table t1(c1 varchar(200))
    insert t1 values ('99TUE'), ('1'), ('TUE'), ('1999TU'),('T7u87h');
    go 
    SELECT DBO.GET_NUMBER(c1)  from t1
    /*
    -----------
    99
    1
    0
    1999
    787
    */
    
    ---your script
    select * from Item ii 
    where ii.QtyCommitted  >= 1 and DBO.GET_NUMBER(ii.CategoryID) <= 84 
    
    
    
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 5, 2019 6:29 AM
  • Thank you for the function. But i need it to include this in my big query 
    Thursday, December 5, 2019 2:46 PM
  • We want to help you but we just guess what you want. You need to tell us what part of CategoryID to compare. For example, the CatoryID "T7u87h", should it be 0 or 7 or 787 you want to compare?

    A Fan of SSIS, SSRS and SSAS

    Thursday, December 5, 2019 4:57 PM
  • >> Conversion failed when converting the varchar value '99TUE' to data type INTEGER. <<

    What did you expect it to become? This is like asking, "on a scale from 1 to 10, what color is your favorite letter of the alphabet?"; It makes no sense. 

    >> Category ID is VARCHAR(50) NOT NULL datatype and QtyCommitted is INTEGER <<

    Please take a basic course in data modeling. There is no such thing as "category_id" in a valid data model; both of those terms are what we call attribute properties and they need to be put on an attribute. That means we need "<something in particular>_category" or "<something in particular>_id". What you've done is much like putting a list of adjectives that have no noun to modify. I would assume that this is a category code, but it is so insanely long. I can't believe you actually did any design or research work whatsoever. I think you simply grabbed the first declaration you could think up and blindly used it. This is incredibly sloppy programming.

    When you finally get around to reading ISO and ANSI standards, one of the things you'll notice is that very very few encoding schemes in the world are that long. I believe there is one used in the banking industry that can get this long, but cannot remember what it is.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 5, 2019 7:23 PM