Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2013
    Posts
    31

    Unanswered: SQL Error :Conversion failed when converting the varchar value 7 1 to int

    Hi All,
    I am new into SQL and I would like to request help Please:
    What is I am trying to Achieve
    When I use a filter using one customer the query return rows of data ok
    When I remove the filter on customer name, the query run for over 15 minutes then throw an error after retrieving more 700 records row data approximately
    Details of error I am getting from SQL 2008 R2
    Msg 245, level 16, State 1 line 16
    Conversion failed when converting the varchar value 7 1 to data type int.
    Line 16:
    Query
    Line 16 start from select stament below
    Select FirstName , ProductName, C +( Convert VARCHAR(20) ,CAST (Customer.orderDate AS DATETIME ),12 +RIGHT (REPLICATE(0,4)+CAST(Customer.Ordernumber as VARCHAR(20)

    As explained earlier when I use AND Clause on Customer Name the Query run OK , then when I remove that fitter I get about 700 row of records below is the summary of the error
    Msg 245, level 16, State 1 line 16
    Conversion failed when converting the varchar value 7 1 to data type int.
    Please note : I am Combining Prefix C with Customer date and order Number
    Result look like
    C120414878:: Mr XY BZ (Customer with order details
    Summary of what is happening
    Please note I am using another filter is Where Clause on the country where Customer from , then used AND clause to filter the customer name , and this is where I am getting an error when I remove clause to filter(AND) customer name :
    example on filter : WHERE country = 'xy' AND Customer firstname = 'bx' the moment I remove the fitter on Customer name ,the query will run then throw an error (Conversion failed when converting the varchar value 7 1 to data type int.
    Any help will be highly appreciated and thanks very much in advance.
    Last edited by john100; 05-14-14 at 12:27.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would suspect this is caused by an implicit conversion in either a JOIN clause, or your WHERE clause. Any chance of posting the full WHERE clause?

  3. #3
    Join Date
    Mar 2013
    Posts
    31
    WHERE (SD_database.Orderdate BETWEEN ‘01/12/1970 AND '31/7/1971')

    Please note this SD_database is another database where I am getting order date data. as said this work ok when I filter records using one customer Name e.g. Mr XY BX , the moment I disable the AND clause on Customer name , the query will run for a about 15 minutes then return about 700 customers(rows of data ) then throw the error .
    Thanks so much for you help much appreciated.
    Last edited by john100; 05-14-14 at 14:08.

  4. #4
    Join Date
    Mar 2013
    Posts
    31
    WHERE (SD_database.Orderdate BETWEEN ‘01/12/1970' AND '31/7/1971')

    Please note this SD_database is another database where I am getting order date data. as said this work ok when I filter records using one customer Name e.g. Mr XY BX , the moment I disable the AND clause on Customer name , the query will run for a about 15 minutes then return about 700 customers(rows of data ) then throw the error .
    Thanks so much for you help much appreciated
    Last edited by john100; 05-14-14 at 14:08.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post your actual SQL, please
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I hope the extra apostrophe between the 12 and the 1970 is a typo.

    The best way to figure out this error is going to be (well, what we would do, anyway) to go through all of the comparisons made in the query, and see where something is compared to an integer. This will include columns. The '7 1' may be from something that was derived in part of the query (maybe a substring of some number that is off by a place).

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Conversion failed when converting the varchar value ‘7 1’ to data type int.
    I thought that value ‘7 1’ might be not an integer nor any number.
    ‘71’ or '701' may be an integer, ‘7.1’ may be a number...

  8. #8
    Join Date
    Mar 2013
    Posts
    31
    Hi MCrowley

    Yes the extra apostrophe between 12 and the 1970 is a typo error , apologies , What I am stuck with is that when I filter the data using one customer the data is correct , then when I Remove the filter on Name the query will run for approx. 15 minutes then retrieves about 700 rows of data then throw an error(Conversion failed when converting the VARCAHR value ‘7 1’ to int) So just wondering does it have to do with WHERE CLUSE or Converting VARCHAR? Much appreciated for your advice

  9. #9
    Join Date
    Mar 2013
    Posts
    31
    Hi Tonkuma
    Thanks for your response
    ---
    What I am stuck with is that when I filter the data using one customer the data is correct , then when I Remove the filter on Name the query will run for approx. 15 minutes then retrieves about 700 rows of data then throw an error(Conversion failed when converting the VARCAHR value 7 1 to int) So just wondering does it have to do with WHERE CLUSE or Converting VARCHAR? Much appreciated for your advice
    ----
    Example
    Select FirstName , ProductName, C +( Convert VARCHAR(20) ,CAST (Customer.orderDate AS DATETIME ),12 +RIGHT (REPLICATE(0,4)+CAST(Customer.Ordernumber as VARCHAR(20)
    From -------Some Statement From Clause --------
    ------------Some inner Join Statements Clause ------
    WHERE (SD_database.Orderdate BETWEEN 01/12/1970' AND '31/7/1971')


    AND Customer.firstname =xy
    AND Customer.surnsme =by

    The results are fine .

    The moment I remove AND Customer.firstname =xy
    AND Customer.surnsme =by , the query run for about 15 minutes then return about 700 customers approx. then throw an error :::Conversion failed when converting the varchar value 7 1 to int
    Last edited by john100; 05-14-14 at 14:20.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by gvee View Post
    Post your actual SQL, please
    This please.

    Not a cut, paste, modify, version. For a start what you've posted above has a syntax error.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •