SQL Server : error on converting varchar to decimal / float -

i getting error while trying convert varchar decimal.

i found place of error unable rectify it.

the code use is:

select      convert(datetime, dbo.payments.recdate, 3) rdate,     convert(decimal, dbo.payments.amount),     dbo.payments.balance      dbo.payments 

i got error message:

error converting data type varchar numeric.

the error occurs due value -5.862 in amount column.

i try changing value -5.862 5 works properly.

anyone please me convert varchar decimal value -5.862

thanks.

the problem here twofold:

1. you're storing numeric values text
2. you're not validating input text column

your comment says value stored this: - 5.862, , not this: -5.862.

so here's sql try out:

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

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

the real answer here not store numeric values text, instead store them in proper numerical data type begin with. barring that, at least validate input, making sure don't allow non-numeric values creeping database in column, not allowing spaces between minus sign , number.

do not let bad data enter table. you're not saving any work doing that. validation , fixups need perform existing data work better placed @ point data enters table.

a "quick fix" akin keeping pants warm this:

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

but postpones problem until enters this:

-5,862 twenty !27 

ie. else cannot converted either. since you're storing them text, need work fix bad values where use them instead of where originate.

however, there's problem, suggested others here, , decimal default doesn't allow digits after decimal point, 1 works above produces -6.

to fix that problem, specify precision:

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

however, , cannot enough: do not store numerical values text. it's disaster waiting happen (which have figured out).