Getting Disallowed implicit conversion from data t

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)

Getting Disallowed implicit conversion from data t

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2006-07-27 : 10:30:42

Hi all,

When I run this query I need to get the result between what ever the user enters the giftamount, which is a decimal datatype and here it is asking me to use Convert function.

Iam getting the following error,

Disallowed implicit conversion from data type varchar to data type money, table 'gifts_full', column 'giftamount'. Use the CONVERT function to run this query.

SELECT DISTINCT

c.coreid,n.nameplural,n.namecsalut,a.addrline1,

a.addrline2,a.addrline3,a.addrcity,a.addrplace,a.addrzipcod,

a.addrcntry,n.namesmashd

FROM corebio_full as c INNER JOIN name_full as n

ON c.coreid = n.nameid JOIN address_full AS a

ON c.coreid = a.addrid JOIN gifts_full AS g

ON c.coreid = g.giftid JOIN attribute_full AS attr

ON c.coreid = attr.attrid

WHERE n.nametype ='a' and a.addrlocatr='good' and a.addrmc1 ='y'

and g.gifttype ='g' or g.gifttype = 'y' or g.gifttype ='c'

or g.gifttype ='b'

and g.giftamount between '25.00'

and '999.99'

and (a.addrcrdate <= getdate())

and (attr.attrstop is null or attr.attrstop > getdate())

and attr.attrtype <> 'anonym'

or attr.attrtype <> 'nomail' or attr.attrtype <> 'nomdon'

and a.addrcounty ='Nantucket'

and gifteffdat between '07/27/2005' and '07/27/2006'

How to convert the amount, i mean decimal to varchar or money.

any help appreciated.

Thanks,

Kristen
Test

22859 Posts

Posted - 2006-07-27 : 10:33:57

If g.giftamount is datatype MONEY you cannot do:


and g.giftamount between '25.00' and '999.99'

because implicit conversion from String to Money is not supported. You would have to do:


and g.giftamount between CONVERT(money, '25.00') and CONVERT(money, '999.99')

Kristen

swenri
Yak Posting Veteran

72 Posts

Posted - 2006-07-27 : 10:59:56

But, the datatype that I have in the table is decimal(19,2)

I tried the same with the query that you sent but, it's not working. It's pulling all the records.It's should be specific to that amount and date that I hard coded.

Thanks,

swenri
Yak Posting Veteran

72 Posts

Posted - 2006-07-27 : 11:01:06

Any help highly appreciated. Please ....

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 11:09:11

Kristen's code probably does work.
You should also be able to
and g.giftamount between 25 and 999.99

If it's giving all the records then it's probably because there's somethiong wrong with the filter you've given.
Try the individual bits of the where clause until you see what's wrong.

I suspect it's because of your or's
instead of
a
and b or c or d
you probably need
a
and (b or c or d)
and e
and ...

shorter notation is
a in ('x','y','z')
instead of
(a = 'x' or a = 'y' or a = 'z')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-07-27 : 11:11:15

What error did my example Cast give please?

"the datatype that I have in the table is decimal(19,2)"

So the datatype for the gifts_full.giftamount column is decimal(19,2), right?

You haven't specified the table owner, so presumably there is only ONE table called gifts_full, and its owned by DBO, correct?

Either way you should explicitly cast all string constants used in non-string expressions / comparisons to prevent any misunderstanding by the parser. In the same vein your string dates should be 'yyyymmdd' format, or explicitly cast with the appropriate conversion, otherwise they are ambiguous and dependant on the dateformat - which some bury might change!

Kristen

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-07-27 : 16:31:56

Kristen,

The point here, though, is that if the datatype for giftamount is money, he shouldn't be using a _string_ constant. He should be just leaving the quotes out entirely.

Ken

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-28 : 01:46:13

SELECT DISTINCT

c.coreid,n.nameplural,n.namecsalut,a.addrline1,

a.addrline2,a.addrline3,a.addrcity,a.addrplace,a.addrzipcod,

a.addrcntry,n.namesmashd

FROM corebio_full as c INNER JOIN name_full as n

ON c.coreid = n.nameid JOIN address_full AS a

ON c.coreid = a.addrid JOIN gifts_full AS g

ON c.coreid = g.giftid JOIN attribute_full AS attr

ON c.coreid = attr.attrid

WHERE n.nametype ='a' and a.addrlocatr='good' and a.addrmc1 ='y'

and g.gifttype ='g' or g.gifttype = 'y' or g.gifttype ='c'

or g.gifttype ='b'

and g.giftamount >= 25 and g.giftamount < 1000

and (a.addrcrdate <= getdate())

and (attr.attrstop is null or attr.attrstop > getdate())

and attr.attrtype <> 'anonym'

or attr.attrtype <> 'nomail' or attr.attrtype <> 'nomdon'

and a.addrcounty ='Nantucket'

and gifteffdat between '07/27/2005' and '07/27/2006'

Use '<' , '=<' , '>' , '>=' operator instead of BETWEEN operator

Mahesh

Kristen
Test

22859 Posts

Posted - 2006-07-28 : 02:06:28

"The point here, though, is that if the datatype for giftamount is money, he shouldn't be using a _string_ constant. He should be just leaving the quotes out entirely."

Indeed, brain not in gear! Thanks.

However, I'm still perplexed as to why he is getting a MONEY conversion error if the column datatype is indeed DECIMAL.

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-28 : 05:14:03

Have a look at my last post - don't think he's telling the whole story.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources