Error converting data type nvarchar to numeric

Question

Post reply

Error converting data type nvarchar to numeric

Chrissy321

SSCoach

Points: 15750
More actions
January 26, 2011 at 10:55 am

#233409

I'm mystified. Please help if you can.
This returns data
SELECT
CONVERT(decimal(6,2),Field1),
Field2
FROM My Table
WHERE Field2 IS NOT NULL
This returns 'Error converting data type nvarchar to numeric'
SELECT
CONVERT(decimal(6,2),Field1),
Field2
FROM My Table
WHERE Field2 IS NOT NULL AND CONVERT(decimal(6,2),Field1) < Field2
Field1 datatype is nvarchar(50).
Field2 is decimal(6,2)
ISNUMERIC(CONVERT(decimal(6,2),Field1)) returns 1 for all fields.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

Chrissy321 (1/26/2011)
ISNUMERIC(CONVERT(decimal(6,2),Field1)) returns 1 for all fields.

ISNUMERIC lies 😎 Don't use ISNUMERIC to validate integer values in a Field.

Use this : LIKE '%0-9%'

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 2

Also i suggest you read thro the following article from one of the Masters of SQL,Mr.Jeff Moden..

Why doesn’t ISNUMERIC work correctly? (SQL Spackle)[/url]

Chrissy321 SSCoach Points: 15750 More actions · Answer 3

Can LIKE '%0-9%' be adapted if my data is like the following?

100.00

75.99

99.00

Chrissy321 SSCoach Points: 15750 More actions · Answer 4

Putting aside ISNUMERIC and its issues I still don't understand why a CONVERT would work when selecting a field but not in the WHERE clause.

Is it possible that the comparison using the CONVERT is being evaluated before the IS NOT NULL. Can I force precedence?

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Chrissy321 (1/26/2011)
Putting aside ISNUMERIC and its issues I still don't understand why a CONVERT would work when selecting a field but not in the WHERE clause.
Is it possible that the comparison using the CONVERT is being evaluated before the IS NOT NULL. Can I force precedence?

In theory you can't force precedence, but you can do something that will have the effect of forcing precedence. Try changing

WHERE Field2 IS NOT NULL AND CONVERT(decimal(6,2),Field1) < Field2

to

WHERE

case

when Field2 IS NULL then cast(0.0 as decimal(6,2))

else CONVERT(decimal(6,2),Field1) end

end

< Field2

This should guarantee that CONVERT is not called when Field2 is null, and when Field2 is NULL it is compared with 0.0 which results in UNKNOWN so that row is excluded from the select.

Tom

Chrissy321 SSCoach Points: 15750 More actions · Answer 6

Chrissy321

SSCoach

Points: 15750

January 27, 2011 at 8:32 am

#1279118

Thank you. Your suggestion is working for me.