I am trying to fix an error but have failed for a while. I keep getting error:
Conversion failed when converting the varchar value ‘789.08’ to data type int.
The Sales column from Table3 is defined as varchar(20) null.
Please not Sales column includes varchar values such as ‘Not Found’ or empty space ‘ ‘, and decimal values such as ‘789.08’. I have never seen this before. I could definitely use anyone’s support.
SELECT DISTINCT
a.AccountNumber,
a.LocationNum,
b.DateSubmitted,
CAST (replace(d.Sales, ',', '') AS int) AS 'Sales',
d.Description
FROM
Table1 a
INNER JOIN
Table2 b ON a.AccountNumber = b.AccountNumber
INNER JOIN
Table3 d ON d.AccountNumber = b.AccountNumber
2
Integers are whole numbers like 1, -1, 42, and 789. These are stored in the various integer types.
789.08 is a decimal number. There’s generally two ways to store them: floating point numbers or arbitrary precision numbers. These are stored as float and decimal or numeric. They both have caveats.
Floating point numbers can be imprecise. If it’s important that 789.08 be exactly represented, for example if it’s currency, use decimal(10,2).
You should provide more information about sales column. If sales column is expecting a decimal value, you have to cast d.Sales from varchar to decimal, not to int. In addittion, if sales column is int-type, you can’t convert 789.08 to int:
select Distinct
a.AccountNumber,
a.LocationNum,
b.DateSubmitted,
CAST(replace(d.Sales, ',', '') AS decimal(10, 2)) as 'Sales',
d.Description
from Table1 a
inner join Table2 b on a.AccountNumber = b.AccountNumber
inner join Table3 d on d.AccountNumber = b.AccountNumber
1