Problem in decimal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem in decimal

Author

Topic

abhi143
Starting Member

32 Posts

Posted - 2006-09-22 : 00:37:53

I have two field one is string data type and another one is money.

Now i want to store the multiplication of first and second field to third field, but when we multiply the first field with second field, we have to always append decimal in front of string and multiply with second field.

ex..

1) first Field = 123456(char(11))
2) Second Field = 123456789012345(money(15))

3) Third Field is money data type(15)

= .123456 x 123456789012345

= .111 x 12412454

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-22 : 00:48:21

[code]
declare @field1 char(11),
@field2 money,
@field3 money

select @field1 = '123456',
@field2 = 123456789012345

select @field3 = convert(money, ('.' + @field1)) * @field2

select @field3
-- 15246913443024.6075
[/code]

abhi143
Starting Member

32 Posts

Posted - 2006-09-22 : 01:25:07

but the correct answer should be 15241481344308.06432
not the query which is giving

abhi143
Starting Member

32 Posts

Posted - 2006-09-22 : 01:28:38

i think float will come in place of money

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-22 : 01:43:29

that's because when you convert 0.123456 to money, you lost the last 2 decimal digits and left with 4 which is 0.1235

So the calc is actually 0.1235 * 123456789012345

abhi143
Starting Member

32 Posts

Posted - 2006-09-22 : 01:52:14

thanks khtan..for your valuable suggestion

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-22 : 01:57:42

You might want to convert to float and calculate before convert back to money. But do take not that float is an approximate value so you might have problem there.

sql_guy
Starting Member

4 Posts

Posted - 2006-09-22 : 05:05:41

any example???????????????

sql_guy
Starting Member

4 Posts

Posted - 2006-09-27 : 04:44:29

Ima getting isssues, when i have used float in place of money.iam getting error "Error converting data type varchar to float."
Code is:-
declare @field1 char(11),
@field2 money,
@field3 money

select @field1 = '123456',
@field2 = 123456789012345

select @field3 = convert(float, ('.' + @field1)) * @field2

select @field3

And one more thing, that decimal digit should not missed(display up to 6 decimal place)

please help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 06:58:07

DECLARE	@FirstField CHAR(11),
	@SecondField MONEY

SELECT	@FirstField = '123456',
	@SecondField = 123456789012345

SELECT	CAST(@SecondField AS BIGINT) / POWER(10.0, LEN(@FirstField)) * CAST(@FirstField AS BIGINT)

Output is

15241481344308.064320

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources