Arithmetic overflow error converting nvarchar to data type numeric when the value is 0

Question

I have the following query returns resulting me Null or Zero:

SELECT TOP 1 ISNULL([jul-12],0) FROM Table_tmp 
WHERE ID = 123250838

but when I add a condition asking if the column value is zero causes overflow error:

SELECT TOP 1 ISNULL([jul-12],0) FROM Table_tmp 
WHERE ID = 123250838
AND [jul-12] <> 0

The data type of the column is FLOAT

exec sp_help 'Table_tmp'
jun-12  float   no  8   53      NULL    yes (n/a)   (n/a)   NULL

I tried with the functions CONVERT () and CAST () but with the same result.

But when the value of the column [Jul-12] is nonzero, it works without errors. Why does this happen?

Can you build a sample Fiddle -- not experiencing the same problem on my end. This can get you started: sqlfiddle.com/#!3/5029c/1 — sgeddes
– sgeddes, Commented Feb 8, 2013 at 15:52

Pandian · Accepted Answer · 2013-02-08 16:00:30Z

1

Try like below... may be it will help you...

SELECT TOP 1 ISNULL([jul-12],0) FROM Table_tmp 
WHERE  Str([jul-12], 25, 5) <> '0'

answered Feb 8, 2013 at 16:00

Pandian

9,1562 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roman Badiornyi · Accepted Answer · 2013-02-08 16:08:21Z

0

This is because of CAST () specification. The result data type will be as in the second parameter. To avoid this you can use COALESCE function which determine result data type by first parameter, also benefit of this function is that it's ANSI standard (instead of ISNULL). you can look for this function here: COALESCE MSDN usage in your case is: COALESCE(jul-12],0) - this will return first not null value.

answered Feb 8, 2013 at 16:08

Roman Badiornyi

1,56915 silver badges29 bronze badges

Comments

nbpeth · Accepted Answer · 2013-12-20 16:10:55Z

0

I just ran across this problem and solved it by throwing an "IS NOT NULL" clause into "WHERE" on the column that I was using cast()

answered Dec 20, 2013 at 16:10

nbpeth

3,1585 gold badges27 silver badges37 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting nvarchar to data type numeric when the value is 0

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related