Convert function for smallmoney

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)

Convert function for smallmoney

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-07-25 : 11:16:03

Hi, I was previously using the float datatype to store money.
Then I switched it to smallmoney. It worked fine with the
float data type.

Now, when I try to insert a record using the
smallmoney datatype , I get the following error

Disallowed implicit conversion from data type varchar to datatype smallmoney. Use the CONVERT function to run this query.

Can someone explain this to me and what I have to do to fix
this problem?

thanks

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-07-25 : 11:22:09

By the way, I am using VB and I am inserting a value of type
single into the smallmoney datatype

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 13:02:40

Float is a variable datatype that can store almost any numeric value. The problem with it is that it's an "approximation" and uses floating point. Smallmoney on the other hand is a very precise datatype. Anywhere you are converting from varchar to smallmoney, you will need to use the following syntax.
[code]
DECLARE
@money VARCHAR(10),
@smallmoney SMALLMONEY

SELECT @money = '15.15'

SELECT @smallmoney = CAST(@money AS SMALLMONEY)

That will get you what you need. Are you using stored procedures, or inserting this directly from VB with inline code?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-07-25 : 14:52:29

Yes, I am inserting the data via VB.

It seems like allot of work to use the money data type.

Is there anything I can do within Enterprise manager one time
to fix this problem for my entire problem.

Also, is double a good replacement for the money datatype?

thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 14:59:03

Why don't you show us some sample data you are trying to insert from your VB application. If the data is formatted correctly, you really shouln't be having any problems. Also, show us the insert code and a snippet from the SQL code or stored procedure.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-25 : 15:07:02

SQL is very fussy about the formatting of data when you use a MONEY type; I don't know why that is, because it doesn't care for other types (i.e. SQL silent does an implicit conversion for Float, Decimal, Datetime, but NOT for Money)

I expect if you use an appropriate VB type the conversion will happen within ADO, rather than you having to explicitly use CAST.

Kristen

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-07-25 : 16:09:41

thanks for you help guys.

I won't be able to send the code until later today.

I was wondering, does the decimal data type give me the
same features as money?

If so, to avoid any hassle, I would use decimal.

In enterprise manager, can I set a decimal datatype to
have the same characteristics as small money?

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 01:00:21

From Books On Line

quote:

money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through
2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.

smallmoney

Monetary data values from - 214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes.

A DECIMAL(10,4) would need 9 bytes, but I expect you could make do with DECIMAL(9,2) which would only need 5 bytes.

In my experience use of DECIMAL() data type is the one that MOST foxes applications - I've used it from VB and PowerBuilder, and both had lots of difficulties with it in the application - so that might only move the CASTing problems from the database to the application. But probably worth a try.

DECIMAL will work with implicit casting, which is what you are after. Money can also handle embedded currency symbol and commas, which decimal cannot.


DECLARE	@Money VARCHAR(10),
	@DecimalMoney DECIMAL(9,2)

SELECT	@Money = '15.15'

SELECT	@DecimalMoney = CAST(@Money AS DECIMAL(9,2))
SELECT	[1]=@DecimalMoney

SELECT	@DecimalMoney = @Money
SELECT	[2]=@DecimalMoney
GO

DECLARE	@Money VARCHAR(10),
	@SmallMoney DECIMAL(9,2),
	@DecimalMoney DECIMAL(9,2)

SELECT	@Money = '$1,234.56'

SELECT	@SmallMoney = CAST(@Money AS SMALLMONEY)
SELECT	[1]=@SmallMoney

-- This will give error
SELECT	@DecimalMoney = CAST(@Money AS DECIMAL(9,2))
SELECT	[1]=@DecimalMoney
GO

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-26 : 01:05:59

DECIMAL is NOT the same as a money datatype. Figure out how to make this work correctly in your application. Also, never make currency a ,2 decimal instead of ,4 if you decide you're going to be lazy and use decimal instead. It could seriously mess up your accounting doing this. That would be fun to explain to auditors.

The money datatypes are designed that way for a reason. Just make them work.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 01:54:08

I thought DECIMAL did accurate maths (i.e. same as MONEY does), whereas FLOAT and DOUBLE are the crappy binary maths that computers use to muck up things with.

What ever happened to Binary Coded Decimal that my Z80 CPU had back ino the 70's? :)

But other than that I absolutely agree with Derrick - use the MONEY data type

Kristen

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 01:54:58

quote:
Originally posted by Kristen

I thought DECIMAL did accurate maths (i.e. same as MONEY does), whereas FLOAT and DOUBLE are the crappy binary maths that computers use to muck up things with.

What ever happened to Binary Coded Decimal that my Z80 CPU had back ino the 70's? :)

But other than that I absolutely agree with Derrick - use the MONEY data type. Switch to using Stored Procedures instead of Dynamic SQL - that will make the problem go away!

Kristen

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-07-26 : 15:23:27

Is I set my decimal datatype to 9,4

what would be the ramifications for using this for money?

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 15:36:05

Well, this is the biggest you can record...

DECLARE @x decimal(9,4)

SELECT @x = 99999.9999

SELECT @x

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources