Error converting data type varchar to numeric issue
-
I tried a lot of things to fix it, and no success.
I have a field called MASTER_BOL_NUMBER . According to documentation it is CHAR.
I see that inside it has only blanks and numbers
When I try to CAST( MASTER_BOL_NUMBER as numeric)
I am getting an error "Error converting data type varchar to numeric."
I tried also smth like that
CAST( IIF(MASTER_BOL_NUMBER='',0,MASTER_BOL_NUMBER) as numeric)
and this
CAST( IIF(MASTER_BOL_NUMBER IS NULL,0,MASTER_BOL_NUMBER) as numeric)
Also no success, I really don't understand why I am getting this error because usually CAST or CONVERT fix this data type issues.
Does someone else know what may help but for those functions?
- This topic was modified 2 years, 7 months ago by JeremyU.
-
You could try using TRY_CAST to determine which row values are causing the CAST to fail
select *
from YourTable
where TRY_CAST(MASTER_BOL_NUMBER as numeric) is null;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
-
If it's the code in your picture which produced the error message then your probable issue imo is a type mismatch between the join columns. Either ar.S1WHS# or wf.L3WHS# is VARCHAR and contains non-numeric values
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
-
I will put this rather more forcefully than Steve Collins.
If the following code gives you a varchar/numeric conversion error, there is no doubt at all that the datatypes of the columns involved in the joins do not match. 100%.
SELECT MASTER_BOL_NUMBER
FROM WF_AR_LD ar
LEFT OUTER JOIN WF_LODM wf
ON ar.AR_LOAD = wf.L3LOD#
AND ar.SlWHS# = wf.L3WHS#;It is very rare that you would join two tables on columns with numeric datatypes. It is bad practice to cast a column as numeric without declaring both precision and scale (see documentation).
Rather than saying things like "According to documentation it is CHAR", please find out for yourself. CHAR columns have a length - you need to know that too. Do you have no access to table structures? Can you execute the following?
SELECT *
FROM sys.columns;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply