locked
Error converting data type varchar to numeric. RRS feed

  • Question

  • User-797751191 posted

    Hi

      I am getting above error on below code - TDS @'+T1.rate + ' %'

    insert into #Tmp
    SELECT 1,'TDS @'+T1.rate + ' %' 
    FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.AbsEntry 

    Thanks

    Monday, February 24, 2020 3:26 PM

All replies

  • User475983607 posted

    jsshivalik

    Hi

      I am getting above error on below code - TDS @'+T1.rate + ' %'

    insert into #Tmp
    SELECT 1,'TDS @'+T1.rate + ' %' 
    FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.AbsEntry 

    Thanks

    Can you at least provide enough code to reproduce the error so the community is not forced to guess?

    I built a test against the AdventureWorks DB and I'm unable to reproduce any errors.

    USE [AdventureWorks2016]
    GO
    
    SELECT 1, 'TDS @'+ T1.[FirstName] + ' %'  
      FROM [Person].[Person] AS T1
    GO
    

    Monday, February 24, 2020 3:35 PM
  • User753101303 posted

    Hi,

    It happens when adding a string and a numeric value. More likely you have to use:

    insert into #Tmp
    SELECT 1,'TDS @'+CAST(T1.rate AS VARCHAR(30)) + ' %' 
    FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.AbsEntry 

    to convert the rate to a string before adding to another string. Though you have also the FORMAT function depending on your SQL Server it is IMO to avoid doing that on the SQL Server side and rather serve "raw" data that are formatted on the client side as you wish:
    - for example it's easier to support multiple languages and have the correct separator
    - you can still apply new rules at any time such as showing the rate using a red color if too low or too high
    - etc...

    IMO try to convert data to a string as late as possible ie whe n you are really about to show them to the user.

    Monday, February 24, 2020 3:57 PM
  • User-797751191 posted

    Hi Patrice

      I want that T1.rate should display only 2 decimal places

    Thanks

    Monday, February 24, 2020 5:09 PM
  • User475983607 posted

    I want that T1.rate should display only 2 decimal places

    Use the standard T-SQL ROUND function.

    ROUND(T1.Rate, 2)



    Monday, February 24, 2020 5:18 PM
  • User753101303 posted

    Which version ? You have also https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15 :

    SELECT 'TDS @'+FORMAT(1.234,'n2','en-us')+' %'

    Monday, February 24, 2020 5:18 PM
  • User-1716253493 posted

    jsshivalik

    Hi Patrice

      I want that T1.rate should display only 2 decimal places

    Thanks

    The problem because second column is number type and your select query return values that cant be converted to a number.

    Change the second column to varchar or modify your select query that return correct number to insert

    Tuesday, February 25, 2020 12:26 AM