Converting a negative number

Question

Post reply

Converting a negative number

krypto69

SSChampion

Points: 13604
More actions
June 28, 2010 at 10:01 am

#226088

We have a nvarchar field that has negative numbers in it (-10,299.00) which is correct.
But we need to convert this to a decimal to be able to add/subtract with another decimal field.
We can't seem to make this work. Is there something special because it has a negative value?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Yes drop the comma. Make sure to always use the period for decimal separator. Then the convert should take.

krypto69 SSChampion Points: 13604 More actions · Answer 2

Sorry it doesn't have commas (my mistake)

so how do we handle the period?

UnionAll SSCrazy Points: 2102 More actions · Answer 3

Try this.

declare @amt nvarchar(100)

set @amt = '-10299.00'

select convert(decimal,REPLACE(@amt,',','')) as Result

--Result -10299

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Then please post sample data that is failing so that we can see the problem.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

select cast(replace(replace(replace('(-10,299.00)', '(',''),')',''), ',','') as decimal(18,2))

Do you have brackets? Can you show exact value you are trying to convert?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

krypto69 SSChampion Points: 13604 More actions · Answer 6

thanks for answering everyone...

here's some sample data -

-95469.49

-92565.24

-90384.38

-89245.69

-88976.89

-82735.84

-79358.26

-78217.29

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

I can't see any problem there:

declare @t table (val nvarchar(50))

insert into @t select '-95469.49'

union select '-92565.24'

union select '-90384.38'

union select '-89245.69'

union select '-88976.89'

union select '-82735.84'

union select '-79358.26'

union select '-78217.29'

select CAST(val as decimal(18,2)) from @t

The above works just fine!

What code you have? What error you get?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

UnionAll SSCrazy Points: 2102 More actions · Answer 8

can you post the result of this query,

select convert(decimal,'-95469.49') as Result

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 9

declare @amt nvarchar(60)

set @amt = '-10299.50'

select convert(decimal(9,2), @amt ) as Result

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

krypto69 SSChampion Points: 13604 More actions · Answer 10

We figured it out

there was a few empty values in that field, if we update to zero it worked fine.

thanks everyone!