IsNumeric question - 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

Transact-SQL (2000)

IsNumeric question

Author

Topic

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2002-07-05 : 13:00:48

I get the following error message when I attempt to run the query shown further below:


Server: Msg 257, Level 16, State 1, Line 5
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.


DECLARE @mymoney1 MONEY
DECLARE @mymoney2 MONEY
SET @mymoney1 = 1.30
SET @mymoney2 = NULL
IF IsNumeric(@mymoney1) <> IsNumeric(@mymoney2)
print 'different'
else
print 'same'

My question is why is it trying to do an implicit datatype conversion to VARCHAR? The docs for IsNumeric in BOL says that money is supposed to evaluate as a valid numeric datatype. I am corn-fused!!!

Edited by - Glockenspiel on 07/05/2002 13:02:14

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-07-05 : 13:51:17

If the two variables are of the MONEY data type then by definition they should be numeric, unless they are NULL. If so use IsNull.

Isn't the purpose of the IsNumeric() function to determine if a value of a non-numeric type can be converted to a numeric type?

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2002-07-05 : 14:34:37

The problem with IsNull() is that you can't rely on logical comparisons if one of the values is NULL; that is, NULL does not equate or disequate with anything (not even itself). The example I provided seems to work fine for all other numeric datatypes except money and smallmoney. It just seems to me that there's something funny about money (sorry, I didn't mean to be punny)!

Edited by - Glockenspiel on 07/05/2002 14:35:20

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-05 : 14:54:30

IsNumeric actually converts to varchar so it can cope with valid characters such as "E" (i.e select IsNumeric(1.23E+111)). If you check the datatype conversion table you will see that money must be EXPLICITLY converted to varchar. This is what is causing the error.

 
DECLARE @mymoney1 MONEY
DECLARE @mymoney2 MONEY
SET @mymoney1 = 1.30
SET @mymoney2 = NULL
If IsNumeric(CAST(@mymoney1 as varchar(10))) <> IsNumeric(CAST(@mymoney2 as varchar(10)))
print 'different'
else
print 'same'

HTH
Jasper Smith

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-07-05 : 14:59:30

Not exactly sure why this wouldn't work:

DECLARE @mymoney1 MONEY
DECLARE @mymoney2 MONEY
SET @mymoney1 = 1.30
SET @mymoney2 = 2
IF (IsNull(@mymoney1,1)=1 OR IsNull(@mymoney2,1)=1)
print 'different'
ELSE
print 'same'

Anyway I think the reason money is funny relative to IsNumeric is that no implicit conversion to varchar is available for money/smallmoney.

If you look up the "CAST and CONVERT" subject in BOL you'll see that all other numeric data types have implicit conversion to varchar.

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2002-07-05 : 16:24:45

quote:

IsNumeric actually converts to varchar so it can cope with valid characters such as "E" (i.e select IsNumeric(1.23E+111)). If you check the datatype conversion table you will see that money must be EXPLICITLY converted to varchar. This is what is causing the error.

Yep; that would do it. Thanks for the clarification.

Edited by - Glockenspiel on 07/05/2002 16:25:16

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2002-07-05 : 16:30:42

quote:

Not exactly sure why this wouldn't work:

DECLARE @mymoney1 MONEY
DECLARE @mymoney2 MONEY
SET @mymoney1 = 1.30
SET @mymoney2 = 2
IF (IsNull(@mymoney1,1)=1 OR IsNull(@mymoney2,1)=1)
print 'different'
ELSE
print 'same'

Josh,

In my particular case I am trying to do a comparison of whether the values are not equal to each other. If I use IsNull and convert the null value to 1 before comparing it to the second value then my inequality evaluates to true when in fact it might be false.

For example:

If a = NULL and b = 1, doing an IsNull(a,1) would convert the NULL value of a to 1 which then makes the inequality statement evaluate to true when in fact it is false.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources