Search code examples
sql-servercastingvarchar

SQL Server Cast varchar column value '1218300.00' as int

A large value in a column has caused my SQL to throw error msg: Error converting data type varchar to numeric

I have isolated this to a particular row. Here is a simplified script that "works":

SELECT 
  MtgeLoanAmount
  , CAST(convert(numeric(15,2),'1218300.00') as int) as TrialValue
FROM dbo.Processed_VA_From_Excel
  where FipsStateCode='06'
  and FipsCountyCode='013' 
  and GuarantyAmount = '304575'

which returns results as pasted here: enter image description here

So when I try to "generalize" my test by adding a 3rd column as follows it fails to convert:

SELECT
MtgeLoanAmount
, CAST(convert(numeric(15,2),'1218300.00') as int)  as TrialValue 
, CAST(convert(numeric(15,2),MtgeLoanAmount) as int)
FROM dbo.Processed_VA_From_Excel
where 
    FipsStateCode='06'
and FipsCountyCode='013' 
and GuarantyAmount = '304575'

returns this:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Solution

  • This may work for you:

    SELECT
  MtgeLoanAmount,
  CONVERT(INT, ROUND(MtgeLoanAmount, 0)) AS MtgeLoanAmountNoCents
FROM
  dbo.Processed_VA_From_Excel
WHERE
  FipsStateCode = '06' AND
  FipsCountyCode = '013' AND
  GuarantyAmount = '304575'