Error Converting Data Type Varchar

How to solve error converting data type varchar to numeric

Best thing is to convert the decimal to string and compare it with the string value.. make necessary adjustments on the decimal part.. :)

SELECT 
    A.SETMOD, B.DESCRP 
FROM 
    PMS.PSBSTTBL A 
JOIN 
    PMS.PR029TBL B ON CONVERT(VARCHAR(50),A.SETMOD) = B.PAYMOD

Error converting data type varchar

OK. I finally created a view that works:

SELECT TOP (100) PERCENT id, CAST(CASE WHEN IsNumeric(MyCol) = 1 THEN MyCol ELSE NULL END AS bigint) AS MyCol
FROM         dbo.MyTable
WHERE     (MyCol NOT LIKE '%[^0-9]%')

Thanks to AdaTheDev and CodeByMoonlight. I used your two answers to get to this. (Thanks to the other repliers too of course)

Now when I do joins with other bigint cols or do something like 'SELECT * FROM MyView where mycol=1' it returns the correct result with no errors. My guess is that the CAST in the query itself causes the query optimizer to not look at the original table as Christian Hayter said may be going on with the other views

T-SQL :: Error converting data type varchar to numeric when changing 1 to 0.5

The error is telling you the conversion is happening in the statement that starts in line 1.

As for why, it's because '' cannot be converted to a numeric but it can be converted to an int.

SELECT CONVERT(decimal(2,1),'')
--Error converting data type varchar to numeric.
GO
SELECT CONVERT(int,'')
--
GO

You were previously converting it to an int (the literal 1 is an int). Get out of the habit of declaring '' for a number; it is by definition not a number, it is a zero length string. If you want zero, then use 0.

SQL Server Error converting data type varchar to bigint from query

Use TRY_CAST() instead:

WHERE TRY_CAST(t.tableNumber AS bigint) = 1190159;

Also, don't compare numbers to strings.

The problem arises because SQL Server does not guarantee the order of evaluation of expressions. So the CAST() can occur before the filtering for the ISNUMERIC().

Error converting data type varchar to numeric in my SQL Server stored procedure

ALTER PROCEDURE [dbo].[spMobileOperationsNew]
@action as nvarchar(50),
@territorycode nvarchar(10)='',
@disYear  as int = '',---?????? may be 0
@disMonth as nvarchar(10)= '',
@territoryTarget as decimal(18,2) ='',---????? may be 0.00
@terDiscRate as  decimal(5,2) ='',--???? may be 0.00
@maxDiscRate as  decimal(5,2) ='',--may be 0.00
@minBill as  decimal(18,2) ='',--may be 0.00
@allocateBudget as  decimal(18,2) ='',--may be 0.00
@actualValue as  decimal(18,2) ='',--may be 0.00
@actDiscValue  as  decimal(18,2) ='',--may be 0.00
@finalBudget as  decimal(18,2) =''--may be 0.00

Error converting data type varchar to date converting an int year into a string

Check out DATEFROMPARTS (available from SQL 2012), and bypass strings altogether.

SET @BeginDate = DATEFROMPARTS(@SchoolYear - 1, 1, 7)

1st arg = year, 2nd = month, 3rd = day.

Error converting data type varchar to numeric while inserting/ updating

These 2 rows contain error:

T.targetValue = CASE WHEN S.targetValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,'')),T.targetValue) END, 
T.actualValue = CASE WHEN S.actualValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,'')),T.actualValue) END, 

Both targetValue and actualValue are decimal(20,3), so how can you use '' that is string with decimals?

It should be

T.targetValue = CASE WHEN S.targetValue is null THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,null)),T.targetValue) END, 
T.actualValue = CASE WHEN S.actualValue is null THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,null)),T.actualValue) END, 

even it has no sense but at least there vill be no conversion from varchar to numeric.

Here is how to reproduce your error (I'll use variables instead of tables):

declare @StargetValue DECIMAL(20,3) = 10, @TtargetValue DECIMAL(20,3) = 20;
set @ttargetValue = CASE WHEN @StargetValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(@StargetValue,'')),@TtargetValue) END 

When you try to confront your decimal with '' you get the error because '' just cannot be converted to decimal.

Error converting data type varchar to int using stored procedure

Dear JParmar,

 INSERT INTO M_ImageHotSpotDetail (HotspotIDFK, XCordinate, YCordinate, HotSpotDescription, CreatedByID, CreatedDate)
            VALUES (@ImageNEWPKId, @ImaheHSXCordinate, @ImaheHSYCordinate, @ImaheHSDisc, @Created_by, GETDATE()) 

For the above line, CreatedByID and CreatedDate column is not exist in M_ImageHotSpotDetail Table.

---

Related Topics