CODEWITHSUNDEEP
sqlsql-server-2005
Bishu
Bishu

Reputation: 133

SQL Server : error on converting varchar to decimal / float

I am getting an error while trying to convert the varchar to decimal.

I found the place of error but unable to rectify it.

The code I use is:

SELECT 
    CONVERT(DATETIME, dbo.PAYMENTS.recdate, 3) AS rdate,
    CONVERT(DECIMAL, dbo.PAYMENTS.amount),
    dbo.PAYMENTS.balance
FROM 
    dbo.PAYMENTS

I got an error message:

Error converting data type varchar to numeric.

The error occurs due to a value -5.862 which is in the amount column.

I try by changing the value -5.862 to 5 then it works properly.

Anyone please help me to convert varchar to decimal with the value -5.862

Thanks.

Upvotes: 1

Views: 16607

Answers (1)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391336

The problem here is twofold:

  1. You're storing numeric values as text
  2. You're not validating the input to that text column

Your comment says your value is stored like this: - 5.862, and not like this: -5.862.

So here's some SQL to try out:

select convert(decimal, '-5.862')
select convert(decimal, '- 5.862')

notice that the first "works" (but lets come back to that), the second throws that exception.

The real answer here is to not store numeric values as text, but instead store them in a proper numerical data type to begin with. Barring that, at least validate the input, making sure you don't allow non-numeric values creeping into the database in that column, like not allowing spaces between the minus sign and the number.

DO NOT let bad data enter your table. You're not saving any work by doing that. The validation and fixups you need to perform to get your existing data to work is much better placed at the point where the data enters your table.

A "quick fix" akin to just keeping your pants warm is this:

select convert(decimal, replace('- 5.862', ' ', ''))

But this just postpones the problem until someone enters this:

-5,862
twenty
!27

ie. something else that cannot be converted either. Since you're storing them as text, you need to do all the work to fix bad values where you use them instead of where they originate.

However, there's another problem, as suggested by the others here, and that is that DECIMAL by default doesn't allow for any digits after the decimal point, so the one that works above produces just -6.

To fix that problem, specify the precision:

select convert(decimal(18, 5), '-5.862')

However, and I cannot say this strongly enough: do not store numerical values as text. It's a disaster waiting to happen (which you have already figured out).

Upvotes: 4

Related Questions