Data types in SQL

SQ · October 1st, 2004, 12:19 AM

Can you help? I'm trying to insert data into a table. When I try to enter values into the money data column, i get the message 'disallowed implicit conversion from data type varchar to data type money, use the convert function to run this query'

What do i need to do?

happygv · October 1st, 2004, 08:19 AM

Can you post on how you are trying to insert values into that column? May be you can post the query here, that you use to do that.

But I assume that you are using Quotes around the value that you try to insert into money data column, from the error you say. You should avoid using quotes in case you use any.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

SQ · October 1st, 2004, 09:11 PM

Thanks for the reply happygv. I am trying to teach myself SQL 2000 and I am just a beginner so your help is really appreciated. What I have tried to enter into a table that has as its dictionary Prod ID-integer (PK),ProdName varchar, Co_ID integer(FK),Capitalisation money.
This is the method I have used
INSERT INTO Products
VALUES ('real estate','543','$575,000.00');
that's when I got the message previosly listed- I won't have access to the table till next wednesday but I will try again removing the quotes from the money value if you think that will help.

happygv · October 2nd, 2004, 05:48 AM

Hi SQ,

My assumption was right in your case.

Code:

INSERT INTO Products
VALUES ('real estate','543','$575,000.00');

Change that to

Code:

INSERT INTO Products
VALUES ('real estate',543,$575000.00);

Also don't use quotes around 543, as ProdId seems to be an integer.

If that returned error, you should sequence the values in the order of columns that are defined in the table structure.

Here is the other way of using this.

Code:

INSERT INTO Products(ProdName, ProdId, Capitalisation)
VALUES ('real estate',543,$575000.00);

If space used within the COLUMNNAME like "Prod ID", use that within []s, but it is a good practice to avoid white space/hyphen usage in COLUMNNAMEs.

Try this in your query analyser.

Code:

exec sp_datatype_info
go

and look for values under LITERAL_SUFFIX and LITERAL_PREFIX columns against "money" value under TYPE_NAME. This would let you know what should be used as prefix and suffix for any datatype, when values are supplied in a query.

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection

SQ · October 3rd, 2004, 02:07 AM

Thanks Vijay, You can't imagine how good it is to find someone who can give you answers.