convert char to money

Question

Post reply

convert char to money

mgraulich

Ten Centuries

Points: 1294
More actions
June 30, 2005 at 4:46 pm

#66716

I need to be able to add a char column that is filled with dollar amounts and also account for the fact there would be decimals. For instance,

What I have :

                           243456

                           234567

What I need it to be:

                          2434.56

                          2345.67

Than I need to take those values with the decimals and add them together to make one single record. Any ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael Du Bois SSCertifiable Points: 5143 More actions · Answer 1

Michael Du Bois

SSCertifiable

Points: 5143

June 30, 2005 at 5:18 pm

#570203

Hi could you post you DDL. Do you need to convert the data to a decimal and move it somewhere? Or do you just need the output in a decimal form?

Mike

Ray M SSC-Insane Points: 21093 More actions · Answer 2

No Idea.

I have no idea what you mean by "Than I need to take those values with the decimals and add them together to make one single record."

Declare @Table table (pk int identity, Value1 varchar(10), Value2 varchar(10))

Insert into @Table (Value1, Value2)

values (243456, 234567)

Insert into @Table (Value1, Value2)

values (666456, 111567)

select convert(money,abs(Value1)* .01), convert(money,abs(Value2) * .01),

Convert(money,(abs(Value1) * .01) + (abs(Value2) * .01))

from @Table

Then you want to add them together and convert it to a Money datatype?

Or are these rows your talking about

243456

234567

If so how do we know what rows to add together?

Please Post a Create Table Statement, with insert statements with the data you see.

Please Read

http://www.aspfaq.com/etiquette.asp?id=5006

mgraulich Ten Centuries Points: 1294 More actions · Answer 3

mgraulich

Ten Centuries

Points: 1294

June 30, 2005 at 6:22 pm

#570210

Basically I need a summation of the converted data to the decimal. So my final output for those two records would be

2434.56

+ 2345.67

4780.23

So the end result would be the summation of the records in decimal form.

Michael Du Bois SSCertifiable Points: 5143 More actions · Answer 4

this should do the trick

hth Mike

drop table #tests

create table #tests

(

valone char(20),

valtwo char(20)

)

insert into #tests values('12345','23456')

insert into #tests values('22345','33456')

insert into #tests values('32345','43456')

insert into #tests values('42345','53456')

--data is stored in one var sum rows

select sum(cast(valone as money))/100 as sum_valone_rows_as_decimal from #tests

Ray M SSC-Insane Points: 21093 More actions · Answer 5

I understand that.

But you need to tell us if 2434.56 and 2345.67 are in 2 different fields in the same row?, or if the 2 values are in the same field on 2 different rows?

Please present us with an example of what the data looks like in the table when you do a

For example

select * from myTable

Field1, Field2, Field3, Field4

1, 234354, 'I Have', 'No Clue'

2 343433, 'I Wish', 'I Did'