Hi,

I have a char field that I need to convert to bigint. I get an arithmetic overflow error. How can I find the values causing the problem? The table is over 200,000 rows.

Thanks

Hi,

I have a char field that I need to convert to bigint. I get an arithmetic overflow error. How can I find the values causing the problem? The table is over 200,000 rows.

Thanks

See if there are any numbers that are too large. BIGINT has 19 digits:

```
SELECT * FROM YourTable
WHERE LEN(REPLACE(YourColumn,'-','')) > 19
```

Not all 19 digit numbers can be expressed as bigint, so if you still don't find anything, convert the string to float and see if it is greater than 2^63-1 or less than -2^63

I now get results:

08754654400416187200

08760009500264266700

05070009500120086500

91950023800002860450

The first 3 are ok because if you remove the leading zero, they are within the range of BIGINT. But the fourth one that starts with 9 has 20 digits and is too large to be stored as a bigint. That will give you an arithmetic overflow.

```
-- this will succeed.
SELECT CAST('08754654400416187200' AS BIGINT);
-- this will cause arithmetic overflow
SELECT CAST('91950023800002860450' AS BIGINT);
```

The largest bigint value allowed is 9,223,372,036,854,775,807 (19 digits). Thus, you can find values that are too large like this:

SELECT *

FROM table_name

WHERE CAST(char_field AS decimal(28, 0)) > 9223372036854775807

If you can have negative values, you'll need to add a check for that as well:

WHERE NOT (CAST(char_field AS decimal(28, 0)) BETWEEN -9223372036854775808 AND 9223372036854775807)

What math are you doing with something lie that?

Also, look up what "field" means in SQL; you meant "column" instead.