Ignore Arithmetic Overflow in microsoft sql server

Question

I'm doing some bit manipulations in SQLServer. And have encountered the famous "Arithmetic Overflow" error. The case is, I want it to overflow. How can I tell it to allow overflow and ignore the error?

I'm doing this select:

SELECT CONVERT(bigint, 0x8000000000000000)-1

I want this to return 9223372036854775807 (the biggest bigint), but it returns:

Arithmetic overflow error converting expression to data type bigint.

I don't know what your needs are, but you're trying to convert the value 0x8000000000000000 to a bigint. That value exceeds the maximum value of what a bigint can be (as you know). But that's why you are getting the overflow. FYI: This works: SELECT CONVERT(bigint, 0x7fffffffffffffff) — Karl, Jan 23 '12 at 12:39

score 4 · Answer 1 · answered Jan 23 '12 at 12:41

4

How about this:

SET ARITHABORT OFF;
SET ARITHIGNORE ON;
SET ANSI_WARNINGS OFF;

SELECT ISNULL(CONVERT(bigint, 0x8000000000000000)-1, 9223372036854775807)

See MSDN references on ARITHIGNORE and ARITHABORT

answered Jan 23 '12 at 12:41

AdaTheDev

142,592
28
206
200

Yeah, I know this is older, but it did the trick for me. A query in an application was failing because of one bad column in one row. In my situation, it's better to have that come back as NULL. Thank you @AdaTheDev! – Barry Sep 17 '14 at 14:36

score 0 · Answer 2 · answered Jan 23 '12 at 12:41

That specific expression fails because the -1 is an int & so you need to;

select CONVERT(bigint, 0x8000000000000000)-CONVERT(bigint, -1)
-9223372036854775807

but that's neither the minimum (because of the -1) or maximum; if you want the +ve maximum;

select CONVERT(bigint, 0x7FFFFFFFFFFFFFFF)
9223372036854775807

Ignore Arithmetic Overflow in microsoft sql server

2 Answers2

Linked