January 26, 2011 at 10:55 am
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.
January 26, 2011 at 11:06 am
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%'
January 26, 2011 at 11:11 am
January 26, 2011 at 11:25 am
Can LIKE '%0-9%' be adapted if my data is like the following?
100.00
75.99
99.00
January 26, 2011 at 12:42 pm
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?
January 26, 2011 at 5:46 pm
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
January 27, 2011 at 8:32 am
Thank you. Your suggestion is working for me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply