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
– Karl, Commented Jan 23, 2012 at 12:39

AdaTheDev · Accepted Answer · 2012-01-23 12:41:40Z

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, 2012 at 12:41

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

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

1 Comment

Barry Over a year ago

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!

Alex K. · Accepted Answer · 2012-01-23 12:41:07Z

0

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

answered Jan 23, 2012 at 12:41

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Collectives™ on Stack Overflow

Ignore Arithmetic Overflow in microsoft sql server

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related